Ranking without ties

[KMont]

Hi,

I am learning DAX and am struggling with an issue ranking a table to get a Top N result.  I have many ties as the data set is not large.  The table is already filtered using the filtering in Power BI Desktop.  I have come across some tutorials, but am running into syntax issues.

I am trying to break the ties through alphabetizing, but I keep getting syntax errors.  It is forcing me to use FILTER(ALL in the DAX measure, but then the filter that is already on the table is removed.

This is what I am using...

Alphabetical Rank =

var CURRENTRX = SELECTEDVALUE('Rx Counts'[drug_generic_name])

VAR Result=

    COUNTROWS(

        FILTER(

            ALL('Rx Counts'[drug_generic_name]),'Rx Counts'[drug_generic_name]<=CURRENTRX))

RETURN

    Result

The FILTER(ALL is removing the filter from my table (which I do not want), but that is the only way that I do not get syntax errors.  It seems to be working otherwise.  As you can see my rx total has ties, the rank column numbers the rows skipping the ties the the alphabet rank breaks the ties, however, it is using my larger data set instead of my filtered data set.

Abbreviation	Rx Total	Rank	Alphabetical Rank
dox	5	1	141
mup	4	2	312
tre	4	2	439
tri	4	2	451
spi	3	5	400
cli	2	6	92
clo	2	6	99
des	2	6	127
amm	1	9	18
ben	1	9	43
cef	1	9	68
cic	1	9	78
des	1	9	128
flu	1	9	186
flu	1	9	193
flu	1	9	201
ket	1	9	264
met	1	9	299
pre	1	9	365
ure	1	9	456

Any help you could provide would be most appreciated!!  Thanks!

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[ERD]

@KMont , first parameter of FILTER should be a table. So you can use VALUES('Rx Counts'[drug_generic_name]) instead of ALL.

[KMont]

I just want the abbreviation column and the Rx Total column.  I am wanting a top N...the first 15 will do sorted descending.  The issue is that the data set is small, and there are Abbreviations where the number ties... the 3 below all have 4 records for example so when there is a tie, I would like to rank alphabetically.

mup
tre
tri

The base table ‘Rx Counts’ is a big table with 204,616 rows.  It includes a unique id on each row with the abbreviation, network, month, year, and complete date.  The visual I have summarizes the count of records by abbreviation, but only includes a specific network.

Here is an example of ‘Rx Counts’

[OwenAuger]

@KMont thanks for that!

I take it that Rx Total is a measure.

I would suggest a measure like this, using the RANK function:

Rank = 
RANK (
    DENSE,
    -- Abbreviations values in overall context of visual
    CALCULATETABLE ( VALUES ( 'Rx Counts'[Abbreviation] ),  ALLSELECTED () ),
    -- Order by [Rx Total] and break ties with Abbreviation
    ORDERBY ( [Rx Total], DESC, 'Rx Counts'[Abbreviation], ASC ) 
)

Then apply a visual level filter (such as <= 15) on the Rank measure.

Does this work at your end? Please post back if needed.

Regards

[OwenAuger]

Hi @KMont 

Either the RANK or ROWNUMBER functions should be useful here, as they allow ranking based on multiple measures/columns.

Could you clarify which columns/measures you want to appear in your final visual (aside from ranking measures), and also show a snapshot of the underlying 'RX Counts' table (santised if needed)?

e.g. Do you want to see

Abbreviation

drug_generic_name

Rx Total

Regards