Dynamic unpivoting with DAX

[Anonymous]

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

[_fm]

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. 

[parry2k]

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