Power bi dynamic ranking with filters and groupby counts

Question

I have a table where I generate counts using COUNTROWS and GROUPBY. I do this because several columns are needed to identify a distinct item for counting. I have a DAX measure of these counts. Now I want to rank the counts with largest count being 1. Once I have the ranks I want to make a bar graph of counts on the x-axis of ranking. I want to be able to filter this graph with other columns from the table and have the ranks auto calculate with the new filters. Thanks in advance for the help.

Edit: to explain what im trying to do better I have the table below to get accurate counts of people i need to groupby first name and last name. Then i need to rank the counts in the example below matt smith would be rank 1 with 2 counts and everyone else would be rank 2. I then want to make a graph with rank as the x axis and counts as the values. I want to be able to dynamically filter the graph with year and grade.

I'm currently stuck at how to set this up or even if its possible. I can get my counts through dax with groupby but I think i need to have ranking as a column to use it on an axis. Would I need a new table to do this? I was able to get counts and rankings on a new table but then i lost the ability to filter all my columns from the original data. — Matthew
Can you show sample data and expected result based on the data? If you want it to be dynamic, you need to use measures. Look at RANKX function to calculate rank for your data. — W.B.

SD_ SD_ · Accepted Answer · 2021-02-15T20:13:00

Add a measure, in my case, Record Count:

Record Count = 

VAR tbl = SUMMARIZE(T
                    , [First_Name]
                    , [Last_Name]
                    , "@Count"
                    , VAR fname = [First_Name]
                    VAR lname = [Last_Name]
                    RETURN
                    CALCULATE(COUNTROWS(T), 'T'[First_Name] = fname, 'T'[Last_Name] = lname )
)

RETURN
CALCULATE(SUMX(tbl, [@Count]))

Then use RANKX in another measure:

Ranking = CALCULATE(RANKX(ALL('T'[Last_Name], T[First_Name]), [Record Count], ,DESC,Dense))

No filters selected in slicer:

"c" selected in grade slicer:

Data Table

Power bi dynamic ranking with filters and groupby counts

1 Answers