cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Variable Top N depending on Column Conditions

Hi Everyone,

I want to create a DAX which gives following result :

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.

2 ACCEPTED SOLUTIONS
Frequent Visitor

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,

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.

Solution Sage

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

If I helped you, click on the Thumbs Up to give Kudos.

6 REPLIES 6
Community Champion

 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!
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

Thanks

Hari

If I helped you, click on the Thumbs Up to give Kudos.

Frequent Visitor

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,

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.

Solution Sage

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

If I helped you, click on the Thumbs Up to give Kudos.

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

Solution Sage

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

If I helped you, click on the Thumbs Up to give Kudos.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors