Forum Discussion

afaherty's avatar
afaherty
Icon for Helper V rankHelper V
1 year ago
Solved

Issue with pivoting via DAX

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...
pivot
  • AntrikshSharma's avatar
    AntrikshSharma
    1 year ago

    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

     

     

divyed's avatar
divyed
Icon for Super User rankSuper User
1 year ago

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's avatar
afaherty
Icon for Helper V rankHelper V
1 year ago

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's avatar
    divyed
    Icon for Super User rankSuper User
    1 year ago

    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.