topic Re: Count and Rank Please in DAX Commands and Tips

Count and Rank Please

Sudhakar510 — Thu, 19 Jan 2023 00:57:51 GMT

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?

Re: Count and Rank Please

Anonymous — Thu, 19 Jan 2023 01:37:06 GMT

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

Re: Count and Rank Please

Sudhakar510 — Thu, 19 Jan 2023 17:31:47 GMT

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.

Re: Count and Rank Please

Sudhakar510 — Tue, 24 Jan 2023 00:10:57 GMT

Thank you Anonymous