cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Need Flag for every unique value

Hi All,

 

I need your help in a logic. I need 1 in front of every unique count in query editor.

 

Here is the output which I need.

 

IDResult
33812741
33814331
33814330
33814330
33815031
33815030
33815030
33822011
33822010
33822010
33822511
33822510
33822510
33822891
33822890
33822890
33823341
33823340
33823340
33830401
33832231
33837461
33843911
33843910
33843910
33845471
33845521
33845891

 

Thanks for your support as always.

 

Regards

Abhijit Mishra

1 ACCEPTED SOLUTION

Accepted Solutions
rajendran Super Contributor
Super Contributor

Re: Need Flag for every unique value

Hi

 

1. Add a index column starting 0( You can do this in Query Editor -> Add columns tab -> index column-> from 0)

2. Create Rank_Flag column with the below formula:

Rank_Flag = IF(calculate(COUNTROWS(Table1),(FILTER(Table1, EARLIER(Table1[Index])>=Sheet4[Table1])),Table1[ID]=EARLIER(Table1[ID])) =1,1,0)

  This will flag the first occurance of the ID . Hope this helps you.

 

Rank.PNG

 

Thanks
Raj

 

View solution in original post

3 REPLIES 3
rajendran Super Contributor
Super Contributor

Re: Need Flag for every unique value

Hi

 

1. Add a index column starting 0( You can do this in Query Editor -> Add columns tab -> index column-> from 0)

2. Create Rank_Flag column with the below formula:

Rank_Flag = IF(calculate(COUNTROWS(Table1),(FILTER(Table1, EARLIER(Table1[Index])>=Sheet4[Table1])),Table1[ID]=EARLIER(Table1[ID])) =1,1,0)

  This will flag the first occurance of the ID . Hope this helps you.

 

Rank.PNG

 

Thanks
Raj

 

View solution in original post

Anonymous
Not applicable

Re: Need Flag for every unique value

Hi @rajendran,

 

Thanks for your reply.

 

Is there a way I can do the second step in query editor also.

 

 

Regards

Abhijit

rajendran Super Contributor
Super Contributor

Re: Need Flag for every unique value

DAX measure is much faster and the calculations happens inmemoy. Is there any specific reason why you want to do this Query Editor?

 

Thanks
Raj

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors