Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Table B, I want to acheive Result and Rank based on Value and ID please
Please any Help?
Solved! Go to Solution.
hello @Sudhakar510
i created a sample data
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])
if i solved you problem, a kudos is well appreciated as well as mark it as solved
Thank you @Anonymous
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.
hello @Sudhakar510
i created a sample data
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])
if i solved you problem, a kudos is well appreciated as well as mark it as solved
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
42 | |
37 | |
22 | |
22 | |
21 |