Dynamic TopN made easy with What If Parameter in Power BI
Dynamic TopN made easy with What If Parameter
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?
Example
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.
Creating the TopN Slicer
- In Power BI Desktop, I went to the Modeling tab
and then clicked on New Parameter
- I then gave it the following
properties as shown below.
- One thing to NOTE is that I set the
Minimum to 0 (zero)
- When you are creating this TopN
What-if parameter, you can change anything for the Maximum, Increment and
Default.
- I could then see the table created on
the right-hand side.
- As well as the Slicer on my reporting
canvas, which I formatted
Creating the TopN Measure
- Below is the TopN measure, which I
will go into detail how it works below.
TopN City =
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
RETURN
SWITCH(TRUE(),
SelectedTop = 0, [Sales Amount],
RANKX (
ALLSELECTED( 'Orders'[City] ),
[Sales Amount]
)
<= SelectedTop,
[Sales Amount]
)
- Line 2, is where I created the Only
Variable called SelectedTop
- This is getting the selected value
from the Slicer.
- If it is slid to 5, this variable SelectedTop will
store 5.
- Line 4 is where I used the SWITCH(TRUE()
- What this does, is it enables me to
pass multiple statements to evaluate in one DAX function.
- NOTE: You could possibly do this with
an IF statement, but I prefer doing it this way, because quite often the
requirement changes to have more than 2 conditions, so doing in this way
it is easy for me to add another condition.
- Line 5 is my first condition, where I
have said if the TopN Slicer (SelectedTop)=
0 (zero) then display all the [Sales Amount]
- It will do this because there is no
filter context being applied on the [Sales Amount]
- Lines 6 – 9 is where the Magic happens
and uses the values from the TopN slicer.
- This is also the second condition for
the SWITCH(TRUE() DAX
expression.
- Even though this is for TopN values,
I use the RANKX to
achieve the desired result from Line 6
- I got this DAX Pattern from SQLBI.COM
- Use of RANKX in Power BI measures
- Next, I am using the ALLSELECTED,
because in my table I want to select the TopN for the City values.
- Line 7 is where I am selecting from
my table Orders and the column called City ALLSELECTED(
'Orders'[City] ),
- Line 8, is where I am specifying my
measure for the RANKX which
is the [Sales Amount]
- Line 9, is where I am closing off the RANKX function.
- Line 10 is where I am now comparing it
to be less than equal to the selected slicer value SelectedTop
- If this evaluates to TRUE, then
display the RANKX up to and including the selected slicer value.
- Line 11 is the ELSE condition for the SWITCH(TRUE()
- Line 12 is closing off the SWITCH DAX
Function
TopN Slicer in Action
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.
Conclusion
As I have shown, by using the What-If Parameter and some DAX there is now a way to easily create a dynamic TopN that is easy for your users to use within their reports. As always, if there are any questions or comments, please leave them in the section below.
Comments
Post a Comment