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

     

     

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

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'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.

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    1 year ago

    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.