Power BI DAX Tutorial: How to PIVOT Data Using DAX (using RANK.EQ function)

Little known fact, I used to box, and boxed a lot… Unfortunately, it did little to help me with my public speaking as you will definitely be able to see from this video where I constantly say UNPIVOT instead of PIVOT. My apologies, hopefully, you will be able to enjoy the video anyway as the topic of the video is actually pretty cool.

How can we take a column of data and PIVOT it in different columns based on the specified criteria? In our case the criteria are based on the Rank of each record. We have several milestone dates for each project, and we want to move our first date into column Milestone 1, second date record for the same project to Milestone 2 column, etc.

We will be using a RANK.EQ() function and having a lot of fun while doing it.

You can download the Power BI Desktop file used in the video here.

This is the DAX code that I use to PIVOT our dates into corresponding columns, one column at a time.

Milestone 1 = 
var currentProjectID = PivotMilestones[ProjectID]

return 
MAXX(
	FILTER(
        CALCULATETABLE(
            ADDCOLUMNS(
	            'Project Milestones',
	            "MilestoneDT", 'Project Milestones'[Milestone Date],
		        "rank", 
                RANK.EQ('Project Milestones'[Milestone Date], 
                    'Project Milestones'[Milestone Date], 
                    ASC)
            ), 
            FILTER(
                All('Project Milestones'),
                'Project Milestones'[ProjectID] = currentProjectID
            )
        ),
        [rank] = 1
	),
	[MilestoneDT]
)