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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Sudhakar510
Helper I
Helper I

Count and Rank Please

Hi Experts,

 

I am looking Count and Rank based on Single Criteria and also combination of 2 Criteria's.


Please see below Table A and Table B.  I am looking to achieve Result and Rank for Table A and Table B Please.

 

Table A I would like to acheive Result and Rank Please based on Values Column

Sudhakar510_1-1674089863487.png

 

Table B, I want to acheive Result and Rank based on Value and ID please

Sudhakar510_0-1674089812159.png

 

Please any Help?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

hello @Sudhakar510 
i created a sample data

eliasayy_0-1674091778528.png

 

the uncessary column is just an added column so the allexcept later on works, because i would assume you have more than those 2 columns 

for table A with only values,use :

 

Count of Items alone = CALCULATE(COUNT(Help[Value]),ALLEXCEPT(Help,Help[Value]))

 

and for ranking

 

table 1 rank = 
VAR __currentvalue = MAX(Help[Value])
VAR __tmpTable = ALL(Help)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,Help[Value],"__Count",[Count of Items alone])
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__Rank",RANKX(__tmpTable1,[__Count],,DESC,Dense))
RETURN MAXX(FILTER(__tmpTable2,Help[Value]=__currentvalue),[__Rank])

 


now for table B:

 

count of item per id = CALCULATE(COUNT(Help[Value]),ALLEXCEPT(Help,Help[ID],Help[Value]))

 

 

but if you only have those 2 columns , id and value, use

 

count of item per id = CALCULATE(COUNT(Help[Value]))

 

 

for ranking:

 

table 2 rank = 
VAR __currentid = MAX(Help[ID])
VAR __currentvalue = MAX(Help[Value])
VAR __tmpTable = ALL(Help)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,Help[Value],Help[ID],"__Count",[count of item per id])
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__Rank",RANKX(__tmpTable1,[__Count],,DESC,Dense))
RETURN MAXX(FILTER(__tmpTable2,AND(Help[Value]=__currentvalue,Help[ID] = __currentid)),[__Rank])

 

 

eliasayy_1-1674092019996.png

 

if i solved you problem, a kudos is well appreciated as well as mark it as solved

 

 

View solution in original post

3 REPLIES 3
Sudhakar510
Helper I
Helper I

Thank you @Anonymous 

Sudhakar510
Helper I
Helper I

Thank you very much @Anonymous , I will give a try with your measure and will update you as soon as possible. My network is super slow today but I am on it and will let you know soom as soon as ETL process finishes.

Anonymous
Not applicable

hello @Sudhakar510 
i created a sample data

eliasayy_0-1674091778528.png

 

the uncessary column is just an added column so the allexcept later on works, because i would assume you have more than those 2 columns 

for table A with only values,use :

 

Count of Items alone = CALCULATE(COUNT(Help[Value]),ALLEXCEPT(Help,Help[Value]))

 

and for ranking

 

table 1 rank = 
VAR __currentvalue = MAX(Help[Value])
VAR __tmpTable = ALL(Help)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,Help[Value],"__Count",[Count of Items alone])
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__Rank",RANKX(__tmpTable1,[__Count],,DESC,Dense))
RETURN MAXX(FILTER(__tmpTable2,Help[Value]=__currentvalue),[__Rank])

 


now for table B:

 

count of item per id = CALCULATE(COUNT(Help[Value]),ALLEXCEPT(Help,Help[ID],Help[Value]))

 

 

but if you only have those 2 columns , id and value, use

 

count of item per id = CALCULATE(COUNT(Help[Value]))

 

 

for ranking:

 

table 2 rank = 
VAR __currentid = MAX(Help[ID])
VAR __currentvalue = MAX(Help[Value])
VAR __tmpTable = ALL(Help)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,Help[Value],Help[ID],"__Count",[count of item per id])
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__Rank",RANKX(__tmpTable1,[__Count],,DESC,Dense))
RETURN MAXX(FILTER(__tmpTable2,AND(Help[Value]=__currentvalue,Help[ID] = __currentid)),[__Rank])

 

 

eliasayy_1-1674092019996.png

 

if i solved you problem, a kudos is well appreciated as well as mark it as solved

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors