DAX : Selecting a measure with a Slicer

Introduction

Someone asked me a question about selecting a measure in Excel with PowerPivot/Tabular and I founded a blog about selecting a measure written by James Thomas. I have made my own example and wrote this blogpost, about it. In this blogpost I'll describe the following steps:

• Create some sample data.
• Create a helper table.
• Create the datamodel.
• Create the measures.
• Create a pivottable and add the properfields to the pivottable.
• Testing the result.

The source data

I've created three tables: DimCustomer, FactSales and a Measure table.

I added these to the PowerPivot model and the datamodel looks like the following screenshot:

The DAX Expressions

I've created the following DAX Expression (the + 2, etc is for getting some different testdata)

MeasureA:=SUM([Amount]) +2      
MeasureB:=SUM([Amount]) + 3
MeasureC:=SUM([Amount]) +8
MeasureD:=SUM([Amount]) +4
       
MeasureValue:=switch (TRUE;
Min ('Measure'[MeasureID] ) = 1;
FactSales[MeasureA];
Min ('Measure'[MeasureID] ) = 2;
FactSales[MeasureB];
Min ('Measure'[MeasureID] ) = 3;
FactSales[MeasureC];
Min ('Measure'[MeasureID] ) = 4;
FactSales[MeasureD])

Configure the Pivottable

This is how the pivottable is setup. The MeasureName is added to the rows and the MeasureValue measure is in the VALUES area.

The result

Here is the result of the solution. Here you can see that the slicerbutton MeasureA and MeasureC selects only the measure A and C and that is exactly what I want.

Yet, another example of selection with the slicerbutton:

Here you can see the different measures according to the selected slicer buttons.

Conclusion

This blogpost is about selecting the proper measures with a slicer.


Greetz,
Hennie