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
In this post I am going to demonstrate how to create a Dynamic TopN slicer using a What-If Parameter. This will allow your user to simply use the Slicer/Slider to view the TopN values and as a bonus if the user slides it to zero, it will display everything! Who doesn't like something that is easy to create, but makes it so much easier for the user to gain insights into their own data easily and quickly?
For this example, I am going to be using a [Sales Amount] measure from my Orders table. Next for the TopN, I am going to be looking for the TopN by City. This is a key component when identifying what you want your TopN to be based on. NOTE: If the explanation of the TopN Measure can be a bit complex, you can either copy the code and modify it for your requirements or you can view the animated GIF further below so you can see it working.
TopN City = VAR SelectedTop = SELECTEDVALUE('TopN'[TopN]) RETURN SWITCH(TRUE(), SelectedTop = 0, [Sales Amount], RANKX ( ALLSELECTED( 'Orders'[City] ), [Sales Amount] ) <= SelectedTop, [Sales Amount] )
Please watch the following animation below in which I will demonstrate by using the Slider I can to from Top 10, to Top 5. And then by sliding it to 0 (zero) it will show all the cities.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.