topic Re: Determine rank of distinct count of values with filter in DAX Commands and Tips

Determine rank of distinct count of values with filter

Anonymous — Wed, 23 Sep 2020 13:09:51 GMT

Hi Community,

I'm trying to calculate the rank of the number of occurrences taking place of email addresses. However, while calculating the rank I also want to ensure a filter is applied (based on who has attended). Below is an example of my dataset

Forum	Email	Attended
Forum 1	aa@abc.com	Attended
Forum 2	aa@abc.com	Not Attended
Forum 1	bb@abc.com	Attended
Forum 3	cc@abc.com	Attended
Forum 1	cc@abc.com	Not Attended
Forum 2	cc@abc.com	Attended

In the above table, the result should show cc@abc.com as Rank 1 as they have attended two forums. However, if I filter on Forum 1, then aa@abc.com & bb@abc.com should be rank 1.

I have written a measure which looks as follows:

RANKX(ALLSELECTED(MyTable),CALCULATE(COUNT(MyTable[Email]),FILTER(ALLEXCEPT(MyTable,Mytable[Email]),MyTable[Attended] = "Attended")),,DESC,Dense)

However, I'm still not able to get a rank which dynamically changes everytime I change a selection.

Can anyone help me out?

Thanks!

Re: Determine rank of distinct count of values with filter

Greg_Deckler — Wed, 23 Sep 2020 13:44:10 GMT

Anonymous Despite it's name, this article actually has quite a bit of good knowlege on how to use RANKX:

https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

@ me if you still need assistance.

Re: Determine rank of distinct count of values with filter

Anonymous — Wed, 23 Sep 2020 20:30:52 GMT

Hi Greg_Deckler

Thanks for sharing this across. I've tried to troubleshoot it using the guide, however, I'm still facing an issue with the ranks.

So what I've tried to also do is break up the calculation into 2 measures: the first one calculates the count with the underlying filter logic, and the second measure ranks the count calculated from the first. I've rewritten the formulas below:

Count = CALCULATE(COUNT(myTable[Email]), FILTER('myTable,myTable[Attendance] = "Attended"))

Rank = RANKX(myTable,[Count],,DESC,Dense)

What I notice is that the count measure works perfectly as I drill across the dataset, so I'm sure the first formula is correct. But everything shows up as a rank 1 something like below:

COUNT RANK

7 1

6 1

5 1

When I try combining the two formulas together, I'm getting the same outcome as above. I'm really not sure what I'm doing wrong over here.

Re: Determine rank of distinct count of values with filter

Greg_Deckler — Thu, 24 Sep 2020 03:12:31 GMT

Anonymous Try:

Rank = RANKX(ALL(myTable),[Count],,DESC,Dense)

Rank = RANKX(ALLSELECTED(myTable),[Count],,DESC,Dense)

Re: Determine rank of distinct count of values with filter

Anonymous — Thu, 24 Sep 2020 06:42:14 GMT

Hi Greg_Deckler yeah I tried your versions of the formula as well, but I'm unfortunately getting the same results.

I did however, manage to find this article which seemed to be doing the trick. From the formulas marked as the solution, I took the version which creates temporary tables, and I changed the sum calculations in the 'summry' table to the count formula I had.

I don't think I would have ever gotten to that answer by myself 😂