It is simple to have multiple visuals for each measure. But what if we want to run multiple measures on one visual? And the measure selection is by using slicer:
There are a few bases we should cover
We have already created three measures for calculating the average, median, and total CTC:
Crore = 10000000 lacs = 100000 Avg CTC (Active,in lacs) = AVERAGEX( FILTER(HRMS,HRMS[Employment Status] = "Active"), HRMS[Current CTC] )/[lacs] Median CTC (Active,in lacs) = MEDIANX( FILTER(HRMS,HRMS[Employment Status] = "Active"), HRMS[Current CTC] )/[lacs] Total CTC (Active, in cr) = SUMX( FILTER(HRMS,HRMS[Employment Status] = "Active"), HRMS[Current CTC] )/[Crore]
Step 1: List of all the measures
Add a simple table in the data model, with no relationship with any other tables. It should contain
· an index column (for sorting),
· and the name of measures (to be used in the slicer)
We can use the Enter Data option for adding a table:
The name of the table in this example is tblCTC_DAX
Create two measures:
Measure Selection = MIN(tblCTC_DAX[Index]) CTC = SWITCH ( [Measure Selection], 1, [Min CTC (Active,in lacs)], 2, [Median CTC (Active,in lacs)], 3, [Total CTC (Active, in cr)], )
Essentially, we are telling Power BI that when “AVG CTC (in lacs)” is selected in the slicer, run the measure [Avg CTC (Active, in lacs)]. So on and so forth. If nothing is selected in the slicer, it will show the minimum CTC by default.
Step 3: Update the visuals
Add a slicer in the page and select the Measure field from the support table:
In all the visuals, we will use the measure created in the previous step [CTC].
It is a handy way of creating a useful user experience.
One set of standard visual and multiple calculations – it is easier to understand and interpret. And not to mention, it saves a lot of time and effort.
Related article: SWITCH DAX Measures in Power Pivot
Also see: Show or Hide Measures using Slicers