Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

  • Anonymous why you cannot use Power Query for the same, why not use the relative tool for unpivot rather than doing it in DAX?

  • Anonymous's avatar
    Anonymous
    Not 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.