Issue with pivoting via DAX

hello afaherty 

if you are planning using DAX, then you need to re-define each subject either in measure or calculated table.

you can see divyed 's solution also define each subject.

if you dont want to define one by one, then you need to do this in PQ like ThxAlot and AntrikshSharma 's solution which is much easier in PQ for data transformation.

Hope this will help.

Thank you.

Hello afaherty ,

I got your point, You want to have a generic query like dynamic pivoting solution. If this is the case Power Query would be the best approach . I am pasting couple of option you can opt for :

1. Power Query : the true generic one 

let
Source = #"Your Data Source Table Name",

// Unpivot the Score and Category columns to create a single 'Value' column
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
Source,
{"StudentID", "Level", "Test"},
"Metric Type",
"Value"
),

// Create dynamic column name: [Test] for score, [Test] Category for category
#"Added Final Pivot Column Name" = Table.AddColumn(#"Unpivoted Other Columns", "Pivot Column Name", each
if [Metric Type] = "Score" then
[Test]
else
[Test] & " Category"
),

// Dynamic Pivot step: This is the generic part!
#"Pivoted Column" = Table.Pivot(
#"Added Final Pivot Column Name",
List.Distinct(#"Added Final Pivot Column Name"[Pivot Column Name]),
"Pivot Column Name",
"Value",
List.Max
)
in
#"Pivoted Column"

2. DAX : You can use external tool to generate repeating string

Pivoted_Report =
SUMMARIZECOLUMNS(
'Test'[StudentID],
'Test'[Level],
"Math",
CALCULATE( MAXX('Test', 'Test'[Score]), 'Test'[Test] = "Math" ),
"Math Category",
CALCULATE( MAXX('Test', 'Test'[Category]), 'Test'[Test] = "Math" ),

"English",
CALCULATE( MAXX('Test', 'Test'[Score]), 'Test'[Test] = "English" ),
"English Category",
CALCULATE( MAXX('Test', 'Test'[Category]), 'Test'[Test] = "English" )

// ... repeat for all other subjects
)

I hope this helps .

Cheers.