CODEWITHSUNDEEP
switch-statementpowerbidaxpowerbi-desktop
coelidonum
coelidonum

Reputation: 543

Making Switch function return a column in a table and not a measure (PowerBI DAX)

What I'm trying to do is to change measures using slicers in Power BI Desktop. I have found some examples of people who have done that (for instance this ) .

What they do is that they create a table where there are ID and Measure names. The 'Measure names' column of this table will be used as field value in the filter of the visualization. Then, they create a switch function that, given a certain value in the filter, switch the value in the filter to a measure. You can see an example below:

Measure Value = SWITCH(
                            MIN('Dynamic'[Measure ID]) ,
                            1,[Max Temp],
                            2,[Min Temp],
                            3,[Air Pressure],
                            4,[Rainfall],
                            5,[Wind Speed],
                            6,[Humidity]
                            )

Where 'Dynamic' is a group containing a measure ID and a Measure name:

Dynamic:

Measure ID | Measure Name
1          |  Max Temp
2          |  Min Temp
3          |  Air Pressure
4          |  Rainfall
5          |  Wind Speed
6          |  Humidity

All of the 'Measure Name' are measures as well.

My problem is: I have too many columns (400!) and I cannot turn them into measures one by one. It will take days. I was thinking that maybe I could use the switch function so that it returns the column in the table and NOT the corresponding measure. However I cannot just insert 'Name of the table'['Name of the column'] in the switch function as result parameter.

Does anyone know how to make the function Switch return a column and not a measure? (Or any other suggestion)

Upvotes: 0

Views: 7879

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

DAX doesn't work well for lots of columns like this, so I'd suggest reshaping your data (in the query editor) by unpivoting all those columns you want to work with so that instead of a table that looks like this

ID | Max Temp | Min Temp | Air Pressure | Rainfall | Wind Speed | Humidity
---+----------+----------+--------------+----------+------------+----------
1  |          |          |              |          |            |
...

you'd unpivot all those data columns so it looks more like this:

ID | ColumnName   | Value
---+--------------+-------
1  | Max Temp     |
1  | Min Temp     |
1  | Air Pressure |
1  | Rainfall     |
1  | Wind Speed   |
1  | Humidity     |
...

Then you can create a calculated table, Dynamic, to use as your slicer:

Dynamic = DISTINCT ( Unpivoted[ColumnName] )

Now you can write a switching measure like this:

SwitchingMeasure = 
VAR ColName = SELECTEDVALUE ( Dynamic[ColumnName] )
RETURN
    CALCULATE ( [BaseMeasure], Unpivoted[ColumnName] = ColName )

where [BaseMeasure] is whatever aggregation you're after, e.g., SUM ( TableName[Value] ).

Upvotes: 1

Related Questions