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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NiMa
Frequent Visitor

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

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


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


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 @jyothi12 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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