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
Step 2:
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
Comments