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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
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!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.