[Power BI] Change Measures/Columns based on Slicer
Assuming we would like to make our Power BI report more interactive by allowing users to choose and show any metrics they need in a visual. That is, we can change measures/columns based on slicer.
We will discuss how to do this in the article.
Step 1. Have the Measures Ready.
For example, if we want to list the Sold QTY and Running 7 days Sold QTY in our visual, then we need to create these two measures first.
Step 2. Create the “Slicer Measure Table” to list out the name of your measures. This column is used to show on the slicer.
- Use “DATATABLE” to create the table. You can use different ways to create the table as long as it is in your data model.
- “Index” here is used to have the measures listed in the sequence we want.
- The “Slicer Measure Table” won’t have any relationship with any other table in our model. Now we can create a slicer on the value column of this table. (Please see step 2–1.)
Step 2-1. Create a Slicer.
Use the “Measures” column to create a slicer as below.
Step 3. Create the “Metrics Columns” measure. This is used to capture the measure in the table visual.
- Use SLECTEDVALUE to connect step 1 (measures) and step 2 (Slicer Measure Table).
- FORMAT is used to format the style in which the numbers appear. For example, percentage, thousand separators, decimals, dollar signs.
- FORMAT(“measure name”,”0.0%”)
- FORMAT(“measure name”,”$0,000")
- FORMAT(“measure name”,”0")
- FORMAT(“measure name”,”0,0")
- FORMAT(“measure name”,”0,0.00")
Conclusion
Now we have Complete all the steps. Below is the final visuals. It looks wonderful!
Here we have “Measures” interactive. We can also make this happen for “Columns” in any physical table. Just follow the same steps!
