Switch TOPN with Field Parameters

amitchandak — Thu, 24 Nov 2022 09:04:10 GMT

TOPN with Field Parameters

Problem Statement: Field parameter has been used to create 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, I and 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 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 TOPN measure like

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])) )

When you select Category

When you select Brand

You can find more details on the blog

The file is attached.

Find all my Medium blogs here

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 User

DAX for SQL Users

topic Switch TOPN with Field Parameters in Quick Measures Gallery

Switch TOPN with Field Parameters

TOPN with Field Parameters