Issue with pivoting via DAX

[afaherty]

Hello,

This is piggy-backing off of a previous post I had made and I continue to struggle which is my fault because my real data has more columns of interest that I did not share (apologies for that).

I have this fake data here BUT it is important to note that my real data has many more students than just #1-4. (It also has many more columns but they are not of interest).

StudentID	Level	Test	Score	Category
1	High School	Math	88	Above Average
1	Middle School	English	90	Above Average
1	High School	Science	83	Average
1	Middle School	Science	89	Above Average
1	High School	Social Studies	97	Above Average
1	Middle School	Social Studies	88	Above Average
2	High School	English	95	Above Average
2	Middle School	English	90	Above Average
2	High School	Social Studies	68	Below Average
2	Middle School	Social Studies	78	Average
3	High School	Math	85	Average
3	Middle School	Math	72	Average
3	High School	English	93	Above Average
3	Middle School	English	90	Above Average
3	High School	Science	86	Above Average
3	Middle School	Science	95	Above Average
4	High School	English	93	Above Average
4	Middle School	English	89	Above Average
4	High School	Science	81	Average
4	Middle School	Science	87	Above Average
4	High School	Social Studies	74	Average
4	Middle School	Social Studies	70	Average

I want each studentID to have only 1 row for high school, 1 row for middle school, and the subjects to be in columns as in the table below. I have pivoted every way I can, and I have tried the DAX solution in my original post suggested by the very kind & helpful @v-dineshya but I am not having success.

Is there a way to achieve this?

StudentID	Level	Math	Category	English	Category	Science	Category	Social Studies	Category
1	High School	88	Above Average	null	null	83	Average	97	Above Average
1	Middle School	null	null	90	Above Average	89	Above Average	88	Above Average
2	High School	null	null	95	Above Average	null	null	68	Below Average
2	Middle School	null	null	90	Above Average	null	null	78	Average
3	High School	85	Above Average	93	Average	86	Above Average	null	null
3	Middle School	72	Average	90	Above Average	95	Above Average	null	null
4	High School	null	null	93	Above Average	81	Average	74	Average
4	Middle School	null	null	89	Above Average	87	Above Average	70	Average

Thank you in advance!

[AntrikshSharma · Accepted solution]

@afaherty This can be easily done with Power Query no need to use DAX for data transformation.

let
    Source = RawData,
    Unpivot = Table.UnpivotOtherColumns ( Source, { "StudentID", "Level", "Test" }, "A", "V" ),
    MergeCols = Table.CombineColumns (
        Unpivot,
        { "Test", "A" },
        Combiner.CombineTextByDelimiter ( "_", QuoteStyle.None ),
        "Merged"
    ),
    Pivot = Table.Pivot ( MergeCols, List.Distinct ( MergeCols[Merged] ), "Merged", "V", ( x ) => x{0}? )
in
    Pivot

[v-sgandrathi]

Hi @afaherty,

I wanted to check if you had the opportunity to review the information provided by @ThxAlot  @divyed  @Irwan. Please feel free to contact us if you have any further questions. 
 

Thank you and continue using Microsoft Fabric Community Forum.

[ThxAlot]

Easy enough,

[divyed]

Hello @afaherty ,

You can use below dax to create a calculated table. 

Pivoted_Report =

SUMMARIZECOLUMNS(

    'Test'[StudentID],

    'Test'[Level],

    // === Math Columns ===

    "Math",

    CALCULATE(

        MAXX('Test', 'Test'[Score]),

        FILTER('Test', 'Test'[Test] = "Math")

    ),

    "Math Category",

    CALCULATE(

        MAXX('Test', 'Test'[Category]),

        FILTER('Test', 'Test'[Test] = "Math")

    ),

    // === English Columns ===

    "English",

    CALCULATE(

        MAXX('Test', 'Test'[Score]),

        FILTER('Test', 'Test'[Test] = "English")

    ),

    "English Category",

    CALCULATE(

        MAXX('Test', 'Test'[Category]),

        FILTER('Test', 'Test'[Test] = "English")

    ),

    // === Science Columns ===

    "Science",

    CALCULATE(

        MAXX('Test', 'Test'[Score]),

        FILTER('Test', 'Test'[Test] = "Science")

    ),

    "Science Category",

    CALCULATE(

        MAXX('Test', 'Test'[Category]),

        FILTER('Test', 'Test'[Test] = "Science")

    ),

    // === Social Studies Columns ===

    "Social Studies",

    CALCULATE(

        MAXX('Test', 'Test'[Score]),

        FILTER('Test', 'Test'[Test] = "Social Studies")

    ),

    "Social Studies Category",

    CALCULATE(

        MAXX('Test', 'Test'[Category]),

        FILTER('Test', 'Test'[Test] = "Social Studies")

    )

)

I hope this solves your issue.

Did I answer your query ? Mark this as solution if this helps, you can say special thank you by giving Kudos :).

Cheers.

[afaherty]

Thank you, thank you. The issue is that this is fake data and my real data has many more subjects other than math, english, science, and social studies.

[divyed]

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.

[Irwan]

hello @afaherty 

you surely can do with DAX with some workaround (although this much easier with PQ).

you need to create measure for each category (math, math category, english, english category, and so on).

Math =
MAXX(
    FILTER(
        'Table',
        'Table'[StudentID]=SELECTEDVALUE('Table'[StudentID])&&
        'Table'[Level]=SELECTEDVALUE('Table'[Level])&&
        'Table'[Test]="Math"
    ),
    'Table'[Score]
)

Math Category =
MAXX(
    FILTER(
        'Table',
        'Table'[StudentID]=SELECTEDVALUE('Table'[StudentID])&&
        'Table'[Level]=SELECTEDVALUE('Table'[Level])&&
        'Table'[Test]="Math"
    ),
    'Table'[Category]
)

Copy the above DAXs for all studies and its category with change in [Test'] column value.

Hope this will help.
Thank you.

[afaherty]

Thank you, thank you. The issue is that this is fake data and my real data has many more subjects other than math, english, science, and social studies.

[Irwan]

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.