Forum Discussion
Dynamic unpivoting with DAX
I have a table, which for reasons, has to be unpivoted in PowerBI using DAX. At the moment, I'm using a simple, standard solution to accomplish this task using the standard SELECTCOLUMNS/UNION combo, something like:
Table =
VAR t1 =
SELECTCOLUMNS ( 'Table', "Date", [Date], "Store", "Store1", "Value", [Store1] )
VAR t2 =
SELECTCOLUMNS ( 'Table', "Date", [Date], "Store", "Store2", "Value", [Store2] )
VAR t3 =
SELECTCOLUMNS ( 'Table', "Date", [Date], "Store", "Store3", "Value", [Store3] )
RETURN
UNION ( t1, t2, t3 )
It works but it's rather ugly and since I have to unpivot a lot of columns it's rather long and unwieldly. I would prefer a more dynamic solution where I could specify the columns to be unpivoted in a table, or in a variable but can't quite work it out.
So far I've come up with the following solution but it still involves hardcoding column names in the measure:
UnpivotExample =
var Fields =
VALUES(FieldList[FieldName]) //Table listing columns to be unpivoted
var tbl =
CROSSJOIN(
SUMMARIZE(
Table,
Table[ID]),
Fields)
return
ADDCOLUMNS(
tbl,
"Date",SWITCH(
[FieldName],
"col1", CALCULATE(FIRSTNONBLANK(Table[col1],1),FILTER(Table,Table[ID]=[ID])),
"col2", CALCULATE(FIRSTNONBLANK(Table[col2],1),FILTER(Table,Table[ID]=[ID]))... etc
Is there a way to make it more dynamic, without hardcoding the column names themselves in the measure? (yes, I know it can be easily done in M but it's a DAX question).
Cheers,
Michal
2 Replies
- parry2k
Super User
Anonymous why you cannot use Power Query for the same, why not use the relative tool for unpivot rather than doing it in DAX?
- AnonymousNot applicable
Hi,
Your example is very helpful but this won't work when you are adding measure inside your selectcolumn function. As I have to create a table with some attribute and values using table A, but it contains measures which I required.