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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors