cancel
Showing results for 
Search instead for 
Did you mean: 

DAX Unpivot

Super User
3585 Views
Super User
Super User

DAX Unpivot

At a recent user group meeting in Columbus Ohio, one of my members presented me with a problem they were having. The member was working with a SharePoint list and attempting to unpivot a number of columns on thousands of list items. For some reason, the unpivoting, specifically, was causing massive performance issues. Therefore, I suggested unpivoting the data in DAX instead and created this example for the member to demonstrate the technique. Fortunately, this worked very well and was very performant even at scale. So, if you are having trouble with unpivoting data in Power Query, this technique may work for you.

 

The original example data looks like this:

Name Date Column1 Column2 Column3 Column4 Column5 Column6
Meeting 1 Monday, January 1, 2018 Greg Jason        
Meeting 2 Friday, January 5, 2018 Jason Frank George Battina    
Meeting 3 Wednesday, January 10, 2018 John Greg Jason Battina George Frank

 

The DAX Unpivot formula is:

 

Table = 
FILTER(
    UNION(
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column1]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column2]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column3]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column4]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column5]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column6])
    ),
    [Column1]<>"")

 

This will make the above data look like this:

Name Date Column1
Meeting 1 1/1/2018 0:00 Greg
Meeting 2 1/5/2018 0:00 Jason
Meeting 3 1/10/2018 0:00 John
Meeting 1 1/1/2018 0:00 Jason
Meeting 2 1/5/2018 0:00 Frank
Meeting 3 1/10/2018 0:00 Greg
Meeting 2 1/5/2018 0:00 George
Meeting 3 1/10/2018 0:00 Jason
Meeting 2 1/5/2018 0:00 Battina
Meeting 3 1/10/2018 0:00 Battina
Meeting 3 1/10/2018 0:00 George
Meeting 3 1/10/2018 0:00 Frank

 

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

DouweMeer Member
Member

Re: DAX Unpivot

Hello @Greg_Deckler ,

 

Just stumbled upon your tip. I already knew it, but I was wondering, as the first section of selectcolumns is the same across all, can you bring in a string value created by DAX and apply it to the last bit? A little bit like using Indirect in Excel.