Determine rank of distinct count of values with filter

[Anonymous]

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! 

[Greg_Deckler]

@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.

[Anonymous]

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

    6                      1

    5                      1

    5                      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.

[Greg_Deckler]

@Anonymous Try:

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

or

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

[Anonymous]

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 😂