Forum Discussion

KMont's avatar
KMont
Frequent Visitor
3 years ago

Ranking without ties

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.
 
AbbreviationRx TotalRankAlphabetical Rank
dox51141
mup42312
tre42439
tri42451
spi35400
cli2692
clo2699
des26127
amm1918
ben1943
cef1968
cic1978
des19128
flu19186
flu19193
flu19201
ket19264
met19299
pre19365
ure19456

 

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

5 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    3 years ago

    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

  • KMont's avatar
    KMont
    Frequent Visitor
    3 years ago

    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's avatar
      OwenAuger
      Icon for Super User rankSuper User
      3 years ago

      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

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    3 years ago

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

  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    2 years ago

    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.