Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 very much @eliasayy , 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |