SWITCH DAX measures in Power Pivot



We have seen how to configure dynamic measure selection using DAX measures in Power BI desktop:


Article: Dynamic Measure Selection

This article explains how to replicate this in Power Pivot. All the steps mentioned in the previous article are applicable here. Let us quickly walk through the steps:

Download Sample File

Step 1: Creating a Support Table containing DAX Measures List













Tip: Assign appropriate table name


Add the table to the data model:


Step 2: Add a DAX measure using SWITCH


 Measure Switch =
 SWITCH (
  TRUE (),
  VALUES ( MeasureTable[Measure Name] ) = "Orders by Ship Date", [Orders by Ship Date],
  VALUES ( MeasureTable[Measure Name] ) = "Returned Orders", [Returned Orders],
  VALUES ( MeasureTable[Measure Name] ) = "Returned Sales Amt", [Returned Sales Amt],
  VALUES ( MeasureTable[Measure Name] ) = "Sales Amount", [Sales Amount],
  VALUES ( MeasureTable[Measure Name] ) = "Total Orders", [Total Orders]
 )
 

Step 3: Add a Slicer in the Pivot Table for Measure Name

Add a slicer with the Measure Name column.


Ensure one item is selected in slicer before we add the DAX measure in the Pivot.


Step 4: Add the DAX measure in the Pivot


And that is it!


Point to Note

Only single-selection works while switching DAX measures, as the SWITCH function returns one scaler value.


So, ensure we have at least one item selected in the slicer, and multi-selection does not work. Otherwise, we get the following error:



Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING