Running count unique flag

[Anonymous]

Hi Guys,

I am new to PBI and need your help, I want to create a flag for unique workorder numbers, It is like creating a Rank for each Wonum. The senario and sample data below, Can you please help on this 

Wonum	Running-Count	Wo Uniquie Flag
1234	1	1
1234	2	0
1234	3	0
1234	4	0
7896	1	1
7896	2	0

Wonum is what I have, A single wonum can be assigned to multiple peoples, I want to create a flag WO unique flag 1/0 for every wonum. Expected output is Wo Uniquie flag. PS: I don't have Running-Count calculated, I only have wonum from which I want tp derive flag. Thanks for your help

[pnvinod · Accepted solution]

Hi,

1. Add a index column from Edit queries tab

2. Add a Rank column 

Rank = calculate(COUNTROWS(Table1),(FILTER(Table1, EARLIER(Table1[Index])>=Table1[Index])),Table1[Wonum]=EARLIER(Table1[Wonum]))

Replace Table1 as appropriate 

[Anonymous]

---

@Anonymous wrote:

Wonum is what I have, A single wonum can be assigned to multiple peoples, I want to create a flag WO unique flag 1/0 for every wonum. Expected output is Wo Uniquie flag. PS: I don't have Running-Count calculated, I only have wonum from which I want tp derive flag. Thanks for your help

---

Hi Movva

  But , your sample data doesnt have the people, its simply assiging 1 for each unique WO and 0 for the duplicates. Is it that you want? If not , please share proper sample data with clear expected output.

Thanks

Raj

[Anonymous]

Hi Raj, Please find the sample data and output I am looking for below, my plan after struggling a lot today was to create a flag for unique WO..as posted the link requesting for possible way to achieve it. Can you please help me.. So tha sample data and the output I am looking for is below, I have already got woassigned flag created at the backend. I don't have running - count, Wo Uniquie Flag, and the calculation for sum(Job duration). PLEASE HELP

Wonum	JobDuraint	woassigned flag	Engineer	Running-Count	Wo Uniquie Flag
1234	5	1	Sandeep	1	1
1234	5	1	Greg	2	0
1234	5	1	Raj	3	0
1234	5	1	Kevin	4	0
7896	10	1	Tom	1	1
7896	10	1	Sam	2	0
7654	50	1	Rahul	1	1
7654	50	0	Sandeep	1	0
Output
Sum(Jobduration) for each distinct WO where woassigned =1	wonum
5	1234
10	7896
50	7654

[Anonymous]

Any help on this please? is this possible?

[pnvinod · Accepted solution]

Hi,

1. Add a index column from Edit queries tab

2. Add a Rank column 

Rank = calculate(COUNTROWS(Table1),(FILTER(Table1, EARLIER(Table1[Index])>=Table1[Index])),Table1[Wonum]=EARLIER(Table1[Wonum]))

Replace Table1 as appropriate 

[Anonymous]

Can I know what do you mean by "Replace Table1 as appropriate" please?

[pnvinod]

in the formula "Table1" should be replaced with the name of the Table used by you

[Anonymous]

Hi, I have created Index column, But when I try to creaate a measure it throws me an below error,

"EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

I tried to create Custom Column in edit quieries but again it throws below error

"Expression.Error: The name 'CALCULATE' wasn't recognized.  Make sure it's spelled correctly."

I tried to create New Column and it throws me below error

"DAX don't support comparing values of type integer with values type of text..."

Can you let me know what I am doing wrong please, I also tried to use format without much success

[Anonymous]

My WONUM is text data type... It will be like MJ12345, I tried to change the data type but it still throws the same error

[pnvinod · Accepted solution]

I have opened  Data View

created New Column from Modelling Tab. I have tried with Text data for Wonum And it is working fine.

Try to create the column with one filter on index and see if it works. Then add the second one.