In Power BI, You can change the visual value dynamically based on slicer value selection.
Suppose you have three measures – Sales, Profit & Discount, and user/ client want to see only selected slicer measure value on visual(one measure value at a time).
Let’s understand how you can be achieve this-
Download the sample Dataset from below link-
Now follow these steps in order to achieve this-
Step-1: Create a static table with measure name.
Under Home tab > Click on Table icon > table dialogue box opens.
Then Change column header name( double click on header name and enter new name), after that enter measure name row wise.
Then provides the table name & click on Load button.
Step-2: Now add one slicer visual into report page and drag static table column “Type” into slicer. And set below properties for slicer.
- General: Choose Orientation : Horizontal &Turn on Responsive.
- Selection controls: Turn on Single select
- Selection header: Turn off selection header
- Items: Allows you to set the font size, font family, color & background color for Text.
Step-3: Create a dynamic measure for value change.
Right click on dataset name > New measure > Enter below DAX code.
Total Amount = VAR TotalSales = SUM ( 'Global-Superstore'[Sales] ) VAR TotalProfit = SUM ( 'Global-Superstore'[Profit] ) VAR TotalDiscount = SUM ( 'Global-Superstore'[Discount] ) VAR SelectMeasure = SELECTEDVALUE ( Navigation[Type] ) RETURN IF (SelectMeasure = "Sales",TotalSales, IF (SelectMeasure = "Profit",TotalProfit, IF (SelectMeasure = "Discount",TotalDiscount, BLANK () )))
- Write DAX to get Total Sales, Profit & Discount and store all three calculation into three separate variables.
- Store Slicer selected value into variable “SelectMeasure”, Navigation is Static table name & Type is column name.
- Use IF condition, Passed measure variable name into condition based on selected values.
Step-4: Now add one clustered column chart into report page and drag Region column to Axis & measure to Values section.
Now it will change the visual value dynamically based on slicer selection.
Refer Power BI other post: Power BI tutorials
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our Contact form , we will revert to you asap.
Yes basically we can create a parameter table hosting the options with datatable, no need for relationship with other tables.
Have your measures ready for each option and then use SWITCH and SELECTEDVALUE together and nest your measures inside.
This video explains it in two ways thoroughly