DAX How to return a table based on a condition - workaround for IF to return a table

Question

How to return DAX table based on a condition? The IF function cannot return a table in DAX.

IF( 1=1, table_1, table_2 )

It raises an error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I would like to use a slicer to choose between multiple tables, which table later can be used for the alternating dynamic filter context in CALCULATE.

CALCULATE( [Measure], 
   IF( Condition, 
       table_1,
       table_2
   )
)

To make the problem more challenging I would like the table_1 and table_2 to have different set of columns. So combination of UNION and FILTER function won't do.

UNION(
   FILTER( table_1,     condition ),
   FILTER( table_2, NOT condition)
)

As a possible approach, we might pick up a measure based on a slicer choice:

IF( Slicer_Selection_Condition, 
      [M1], // Measure with filter set 1
      [M2]  // Measure with filter set 2
   )

But I do not want to go this way because it multiplies the number of required measures for each slicer combination.

If we could surpass the IF limitation, we could very usefully apply it. Imagine, we have a slicer to choose a measure among [Quantity], [Value], [Cost]. And we also have another slicer to choose the filter set. We could handle it with a one measure:

CALCULATE(
   SWITCH( Measure_Slicer, 1, [Quantity], 2, [Value],  [Cost] ), // measure choice
   SWITCH( Filter_Slicer,  1, table_1,    2, table_2,  table_3 ) // filter choice
)

Here is a table to recreate problem:

Table = 
DATATABLE (
    "Color", STRING,
    "Shape", STRING,
    "Quantity", INTEGER,
    "Value",    INTEGER,
    {
        { "Red"   , "Circle"  , 1, 10 },
        { "Red"   , "Triangle", 1, 10 },
        { "Blue"  , "Circle"  , 1, 10 },
        { "Blue"  , "Triangle", 1, 10 },
        { "Yellow", "Square"  , 1, 10 }
    }
)

And measures:

M_Quantity = SUM( 'Table'[Quantity] )

M_Value    = SUM( 'Table'[Value] )

Desired Measure = 
VAR Measure_Slicer = 1   // Either 1 OR 2
VAR Filter_Slicer  = 1   // Either 1 OR 2

VAR table_1 = SUMMARIZE( 'Table', 'Table'[Color] )
VAR table_2 = SUMMARIZE( 'Table', 'Table'[Color], 'Table'[Shape] )

RETURN
CALCULATE(
   SWITCH( Measure_Slicer, 1, [M_Quantity], [M_Value]), // Measure choice
   SWITCH( Filter_Slicer,  1, table_1     , table_2  )  // Filter choice
)

user_12025077 · Answer

Here, a solution for one slicer which lets the user choose between different measures. How to select different data tables, is something I am also curious about :)

Based on your table Table above and the two measures M_Quantity and M_Value, the only things you need to add are:

A table which includes the possibilities for the selection:

choice_measures = 
DATATABLE (
    "Id", INTEGER,
    "Use", STRING,
    {
      { 1, "Quantity"},
      { 2, "Value"   }
    }
)

One more measure:

M_Measure = 
VAR MySelection = SELECTEDVALUE(choice_measures[Id], "Show all") RETURN 
SWITCH(
    TRUE(), 
    MySelection = 1, [M_Quantity], 
    MySelection = 2, [M_Value], 
    ""
)

A slicer for choice_measures[Id] where the user can choose between the two measures and, finally, a card where the chosen measure is displayed via M_Measure. Then, it will look like this:

Good luck! Looking forward to the answers of other users on the second part of your question learning how to switch between different data tables!

DAX How to return a table based on a condition - workaround for IF to return a table

1 Answers