Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
10-04-2022 08:56 AM - last edited 11-24-2022 01:04 AM
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-fc4095ae9af...
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