Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap

Reply
Anonymous
Not applicable

Variable Top N depending on Column Conditions

Hi Everyone,

I want to create a DAX which gives following result :

 

NiMa_0-1655300179742.png

 

So based on the "total" I want to find out "Top 3 brand" for "Category A and B" and "Top 1 Brand" for "Category C".

 

I am able to show top 3 Brands across all categories but facing difficulty with variable Top selection based on Category.

Please help.

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Thanks Hariharan_R. This measure is absolutely correct. But i have one more doubt for the same. 

How to show multiple Categories in this DAX where i need this condition of variable Top N . For eg,

 

NiMa_0-1655302444057.png

I have Category D and E as well along with C where this filteration of top N is different as compared to Category A and B.

 

Thanks.

View solution in original post

Hi,

If you have more values then try use disconnected table with the TOPN values otherwise try the below one.

 

Top 3 = 
VAR _N = 
SWITCH(TRUE(), MIN('Table'[Category])="C",2,MIN('Table'[Category])="D",1,MIN('Table'[Category])="E",1,3)
VAR Top3 =
    CALCULATETABLE (
        GENERATE (
            VALUES ('Table'[Category] ),
            TOPN (
                _N,
                CALCULATETABLE ( VALUES ('Table'[Brand] ) ),
                [Sales]
            )
        ),
        ALLSELECTED()
    )
RETURN
    CALCULATE (
        1 * ( NOT ISEMPTY ( 'Table' ) ),
        KEEPFILTERS ( Top3 )
    )

 

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


My Blog :: YouTube Channel :: My Linkedin


View solution in original post

6 REPLIES 6
CNENFRNL
Community Champion
Community Champion

Record_2022_06_15_16_08_52_154.gif


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Hariharan_R
Solution Sage
Solution Sage

Hi,

You can use below measure.

 

Top 3 = 
VAR _N = IF(MIN('Table'[Category])="C",2,3)
VAR Top3 =
    CALCULATETABLE (
        GENERATE (
            VALUES ('Table'[Category] ),
            TOPN (
                _N,
                CALCULATETABLE ( VALUES ('Table'[Brand] ) ),
                [Sales]
            )
        ),
        ALLSELECTED()
    )
RETURN
    CALCULATE (
        1 * ( NOT ISEMPTY ( 'Table' ) ),
        KEEPFILTERS ( Top3 )
    )

 

Sample Data

Hariharan_R_1-1655301607040.png

 

Hariharan_R_0-1655301585856.png

Thanks

Hari

 

 

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


My Blog :: YouTube Channel :: My Linkedin


Anonymous
Not applicable

Thanks Hariharan_R. This measure is absolutely correct. But i have one more doubt for the same. 

How to show multiple Categories in this DAX where i need this condition of variable Top N . For eg,

 

NiMa_0-1655302444057.png

I have Category D and E as well along with C where this filteration of top N is different as compared to Category A and B.

 

Thanks.

Hi,

If you have more values then try use disconnected table with the TOPN values otherwise try the below one.

 

Top 3 = 
VAR _N = 
SWITCH(TRUE(), MIN('Table'[Category])="C",2,MIN('Table'[Category])="D",1,MIN('Table'[Category])="E",1,3)
VAR Top3 =
    CALCULATETABLE (
        GENERATE (
            VALUES ('Table'[Category] ),
            TOPN (
                _N,
                CALCULATETABLE ( VALUES ('Table'[Brand] ) ),
                [Sales]
            )
        ),
        ALLSELECTED()
    )
RETURN
    CALCULATE (
        1 * ( NOT ISEMPTY ( 'Table' ) ),
        KEEPFILTERS ( Top3 )
    )

 

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


My Blog :: YouTube Channel :: My Linkedin


Anonymous
Not applicable

your dax function is working fine but i have the categories 6(a,b,c,d,e,f) for each category top 5 brands i want baced on total sales

Hi @Anonymous 

You can remove _N with 5 like below.

 

CALCULATETABLE (
        GENERATE (
            VALUES ('Table'[Category] ),
            TOPN (
                5,
                CALCULATETABLE ( VALUES ('Table'[Brand] ) ),
                [Sales]
            )
        ),
        ALLSELECTED()
    )
RETURN
    CALCULATE (
        1 * ( NOT ISEMPTY ( 'Table' ) ),
        KEEPFILTERS ( Top3 )
    )

 

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


My Blog :: YouTube Channel :: My Linkedin


Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.