Power BI — show and hide measures on slicer and visual
UPDATE: Microsoft published a baked in functionality a Field Parameter, you can check it out in this article: A field parameter.
In some cases you want to see all measures on your visualizations. In some instances you want to manipulate a visibility of your calculations. Today I tell you how you can show or hide measures depending on your needs.
The first step is to create standard measures:
Sum of sales = SUM(Iowa_Liquor_Sales[Volume Sold (Liters)])
Avg of sales = AVERAGE(Iowa_Liquor_Sales[Volume Sold (Liters)])
Count of sales = COUNTROWS(Iowa_Liquor_Sales)
Max of sales = MAX(Iowa_Liquor_Sales[Volume Sold (Liters)])
Min of sales = MIN(Iowa_Liquor_Sales[Volume Sold (Liters)])
The next step, we need to create a table containing measures names. This table (column) is used to integrate values into a slicer. And to establish a column values on visualizations.
Sales select measure =
DATATABLE(
"Measure Name", STRING,
{
{"Sum of sales"},
{"Avg of sales"},
{"Count of sales"},
{"Max of sales"},
{"Min of sales"}
}
)
Moving forward we have to create the measure, which is responsible for showing or hiding selected measures.
Select measure =
SWITCH(
TRUE(),
SELECTEDVALUE('Sales select measure'[Measure Name]) = "Sum of sales", [Sum of sales],
SELECTEDVALUE('Sales select measure'[Measure Name]) = "Avg of sales", [Avg of sales],
SELECTEDVALUE('Sales select measure'[Measure Name]) = "Count of sales", [Count of sales],
SELECTEDVALUE('Sales select measure'[Measure Name]) = "Max of sales", [Max of sales],
SELECTEDVALUE('Sales select measure'[Measure Name]) = "Min of sales", [Min of sales],
BLANK()
)
Once we have everything ready. We can create visualizations: a matrix and a line chart.
Put a chosen category label into the rows (matrix) and axis (line chart) section.
Locate a [Measure name] column from the previously created the [Sales select measure] table, place into the columns (matrix) and columns (line chart) section.
Insert the [Select measure] measure into the values field (in both cases).
A matrix setup:
A line chart settings:
And here is a demo.