Pivot and Unpivot in DAX

Today, let's take a look at how to do Pivot and Unpivot in DAX. Ideally you would do this in SQL or Power Query but it never hurts to see how you could do same with DAX, infact you might get a better understanding of a very special DAX function.

For Pivoting we are working with StudentsScore data.

To PIVOT in SQL we could write following 2 queries that return different results depending on the requirement

SELECT * 
FROM StudentScores AS S
PIVOT (
    SUM(Score)
    FOR Subject IN (English, Math, Science)
) AS Pvt

SELECT * 
FROM (SELECT StudentName, Subject, Score FROM StudentScores) AS S
PIVOT (
    SUM(Score)
    FOR Subject IN (English, Math, Science)
) AS Pvt

Now DAX doesn't have any PIVOT or UNPIVOT functions but in SQL there is a trick to PIVOT that we can replicate in DAX.

SELECT 
    StudentId, 
    StudentName,
    Term,
    English = SUM(CASE WHEN Subject = 'English' THEN Score END),
    Math = SUM(CASE WHEN Subject = 'Math' THEN Score END),
    Science = SUM(CASE WHEN Subject = 'Science' THEN Score END)
FROM StudentScores
GROUP BY 
    StudentId, StudentName, Term

Now that is something we can replicate that in DAX.

Another way to write this is through GENERATE and ROW, which when paired together act just like ADDCOLUMNS, I could've used the IF function but I thought of keeping it different.

You can make it somewhat better by using UDFs. Major benefit of GENERATE + ROW is that you can store Column references in Variables and then use for every new column that you add, something that isn't possible in ADDCOLUMNS.

ADDCOLUMNS is same as SELECT in SQL whereas GENERATE is equivalent of CROSS APPLY that allows creating intermediate calculations so that you don't repeat them in SELECT.

DEFINE
    FUNCTION FxGetScores = (
        SubjectCol : SCALAR VAL,
        Subject : STRING VAL,
        ScoreCol : SCALAR VAL
    ) =>
        ( SubjectCol = Subject ) * ScoreCol

EVALUATE
	GENERATE (
	    StudentScores,
	   	VAR Subj = StudentScores[Subject]
	   	VAR Score = StudentScores[Score]
	   	RETURN
			ROW (
		        "English", FxGetScores ( Subj, "English", Score ),
		        "Math", FxGetScores ( Subj, "Math", Score ),
		        "Science", FxGetScores ( Subj, "Science", Score )
		    )
	)
ORDER BY StudentScores[StudentId]

Now we need to find a way to aggregate the rows because right now we have only extracted values into 3 different columns.

And for that we are going to use GROUPBY which allows grouping by Local or Virtual columns and exposes a specific Keyword - CURRENTGROUP() that allows iterating and aggregating the table in place.

I have ommitted the table name within ADDCOLUMNS to fit the code in a single line.

DEFINE
	VAR PivotScores = 
		ADDCOLUMNS ( 
			StudentScores,
			"English", IF ( [Subject] = "English", [Score] ),
			"Maths", IF ( [Subject] = "Math", [Score] ),
			"Science", IF ( [Subject] = "Science", [Score] )
		)
	VAR AggregateScores =
		GROUPBY ( 
			PivotScores,
			StudentScores[StudentID],
			StudentScores[StudentName],
			StudentScores[Term],
			"English", SUMX ( CURRENTGROUP(), [English] ),
			"Maths", SUMX ( CURRENTGROUP(), [Maths] ),
			"Science", SUMX ( CURRENTGROUP(), [Science] )
		)
		
EVALUATE AggregateScores

ORDER BY StudentScores[StudentId]

Now time to UNPIVOT.

In SQL we can use these 2 patterns for UNPIVOTing.

SELECT * 
FROM StudentScoresPivoted
UNPIVOT (
	Score
	FOR Subject IN (English, Math, Science)
) AS Unpvt

SELECT 
	S.StudentId, 
	S.StudentName, 
	S.Term, 
	T.* 
FROM StudentScoresPivoted AS S
CROSS APPLY ( 
	VALUES 
		('Subject', [English]),
		('Subject', [Math]),
		('Subject', [Science])
) AS T(Subject, Score)

The first one isn't available in DAX, but as I stated earlier GENERATE is equivalent of CROSS APPLY in DAX so we can use the second technique.

To solve it in DAX we start iteration with GENERATE and then in the second argument prepare a table of 3 rows and 2 columns, First Column will contain the 3 subjects and Second column will contain the Score of that subject extracted from the Row Context.

EVALUATE
	GENERATE (
	    StudentScoresPivoted,
	    SELECTCOLUMNS (
	        {
	            ( "English", [English] ),
	            ( "Math", [Math] ),
	            ( "Science", [Science] )
	        },
	        "Subject", [Value1],
	        "Score", [Value2]
	    )
	)
ORDER BY StudentScoresPivoted[StudentId]

SQL Script for PIVOT:

CREATE TABLE StudentScores (
    StudentId   INT,
    StudentName VARCHAR(50),
    Term        VARCHAR(10),
    Subject     VARCHAR(20),
    Score       INT
);

INSERT INTO StudentScores 
VALUES
    (1, 'Alice',   'Fall',   'Math',    85),
    (1, 'Alice',   'Fall',   'Science', 92),
    (1, 'Alice',   'Fall',   'English', 78),
    (2, 'Bob',     'Fall',   'Math',    74),
    (2, 'Bob',     'Fall',   'Science', 88),
    (2, 'Bob',     'Fall',   'English', 81),
    (3, 'Charlie', 'Fall',   'Math',    95),
    (3, 'Charlie', 'Fall',   'Science', 90),
    (3, 'Charlie', 'Fall',   'English', 87),
    (4, 'Diana',   'Fall',   'Math',    68),
    (4, 'Diana',   'Fall',   'Science', 72),
    (4, 'Diana',   'Fall',   'English', 75),
    (5, 'Ethan',   'Spring', 'Math',    80),
    (5, 'Ethan',   'Spring', 'Science', 84),
    (5, 'Ethan',   'Spring', 'English', 90),
    (3, 'Charlie', 'Spring', 'Math',    50),
    (3, 'Charlie', 'Spring', 'Science', 60),
    (3, 'Charlie', 'Spring', 'English', 55);

Unpivot:

CREATE TABLE StudentScoresPivoted (
    StudentId   INT,
    StudentName VARCHAR(50),
    Term        VARCHAR(10),
    Math        INT,
    Science     INT,
    English     INT
);

INSERT INTO StudentScoresPivoted 
VALUES
    (1, 'Alice',   'Fall',   85, 92, 78),
    (2, 'Bob',     'Fall',   74, 88, 81),
    (3, 'Charlie', 'Fall',   95, 90, 87),
    (4, 'Diana',   'Fall',   68, 72, 75),
    (5, 'Ethan',   'Spring', 80, 84, 90),
    (3, 'Charlie', 'Spring', 50, 60, 55);