Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

Anonymous
Not applicable

Hi @Anonymous,

 

Thanks for your reply.

 

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

 

 

Regards

Abhijit

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.