Show or Hide measures using Slicer: Power BI

Using slicers, we can control the measure to display in a visual:

Unlike the method explained in the previous article, this method enables multiple selections of measures.

Let us see how to set this up.

Like the previous article, we add a disconnected table in the model with the list of all the required measures

The name of the table in this example is Measure Selection Table

Add a slicer on the page and use the column Measure from the table created in the previous step:

Original measures:

% Orders Returned =
  DIVIDE([Returned Orders],[Total Orders])
 
  % Sales Refund =
  DIVIDE([Returned Sales Amount],[Sales Amount])
 

For this exercise, we need to modify the above measures using IF and CONTAINS

% Orders Returned =
  IF(
  CONTAINS('Measure Selection Table','Measure Selection Table'[Index],2),
  DIVIDE([Returned Orders],[Total Orders])
  )
 

In simple English, we are telling Power BI to run the calculation of % Order Returns, IF the slicer selection is Order Returned (corresponding Index row value = 2).

CONTAINS DAX returns true if values for all referred columns exist, or are contained, in those columns; otherwise, the function returns false.

Syntax

It has 3 required arguments: Table name, Column name, and the corresponding value.

For example, in the following formula, we are asking DAX to check in the table Measure Selection Table check for value 2 in the Index column.

CONTAINS('Measure Selection Table’, ‘Measure Selection Table'[Index],2)

Modify the other measures accordingly:

% Sales Refund =
  IF(
  CONTAINS('Measure Selection Table','Measure Selection Table'[Index],3),
  DIVIDE([Returned Sales Amount],[Sales Amount])
  )
 
 
 
  Margin % =
  VAR _SalesAmt =
  SUMX(dtOrders,dtOrders[Unit Price] * dtOrders[Order Quantity])
 
  VAR _TotalCost =
  SUMX(dtOrders,dtOrders[Unit Cost] * dtOrders[Order Quantity])
 
  VAR _Margin =
  _SalesAmt - _TotalCost
 
  VAR _MarginPrcnt =
  DIVIDE(_Margin, _TotalCost)
 
  VAR _Show =
  IF(
  CONTAINS('Measure Selection Table','Measure Selection Table'[Index],1),
  _MarginPrcnt
  )
 
  RETURN
  _Show
 

And that is it!

Download sample pbix file

@imvivran