Switch TOPN with Field Parameters

Problem Statement: Field parameter used in Axis/Dimension slicer. We would like TOPN to work based on Axis. We have two columns Brand and Category with a measure, net. And the Field parameter is created on Brand and Category. Select any one of these and the net’s topn of that should be shown based on that.

Model: I am using the standard sales model,which I am using for all my videos and blogs. Sales fact with a key measure [net], joined with dimensions: Item, Geography, Date, and Customer.

Solution:

Created a field parameter on Brand and category from the Item Dimension. Refer to this blog to learn how to create field parameters: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9afd

This is the table code

Parameter = {
    ("Brand", NAMEOF('Item'[Brand]), 0),
    ("Category", NAMEOF('Item'[Category]), 1)
}

Created following topn measures

TOP2 Brand = CALCULATE([Net], TOPN(2,ALLSELECTED('Item'[Brand]), [Net],DESC), values('Item'[Brand])) 
TOP2 Category = CALCULATE([Net], TOPN(2,ALLSELECTED('Item'[Category]), [Net],DESC), values('Item'[Category]))

Created a selectedvalue for the Axis parameter. As a direct selectedvalue not supported on the Field Parameter column, used this code

Selected Parameter = maxx(filter(Parameter ,Parameter[Parameter Order] =  SELECTEDVALUE(Parameter[Parameter Order])), Parameter[Parameter])

Created a measure which can switch based on selected Axis/Dimension

TOP N = SWITCH([Selected Parameter], "Brand" ,[TOP2 Brand], [TOP2 Category])

When you select Category

When you select Brand

You can only one measure having the complete code

TOP2 Quick = SWITCH([Selected Parameter], "Brand" , CALCULATE([Net], TOPN(2,ALLSELECTED('Item'[Brand]), [Net],DESC), values('Item'[Brand])) ,
CALCULATE([Net], TOPN(2,ALLSELECTED('Item'[Category]), [Net],DESC), values('Item'[Category]))
)

You can find the file here. Comment and let me know which one you liked.

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share

Master Power BI | Expertise Power BI | Power BI For Tableau Users | DAX for SQL Users