CODEWITHSUNDEEP
powerbidaxpowerquery
Peter Hui
Peter Hui

Reputation: 119

Top 2 by group in DAX Power BI

I am trying to get the Top 2 units by company here. Table is called 'Table (3)'

enter image description here

I want to be able to populate the column like this -

enter image description here

I had tried Column = RANKX(ALLEXCEPT('Table (3)','Table (3)'[Company]),SUM('Table (3)'[Units])) but got a circular error.

The other way I think of doing this - not very effective - is use the TOPN and do a UNION by each company so each company would have a table of it's own TOPN value.

I know how to do this on power query already using Table.MaxN but want to do this on DAX

Upvotes: 1

Views: 947

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You can get the rank like this:

Rank = 
VAR CurrUnits = 'Table (3)'[Units]
RETURN
    CALCULATE (
        RANK.EQ ( CurrUnits, 'Table (3)'[Units] ),
        ALLEXCEPT ( 'Table (3)', 'Table (3)'[Company] )
    )

From there, you can throw away ranks other than 1 and 2 if you choose to.

Upvotes: 1

Related Questions