Forum Discussion

CR's avatar
CR
Resolver II
6 years ago
Solved

Pivot data in dax

Hello Everybody,

 

I've no access to the query editor (so DAX) and I got the following table1

 

TypePhase1Phase2Phase3
Type112  
Type2 10 
Type3 814

 

I want the following table2 (pivot on colums Phase1/2/3 and sum on Data by grouping on type).

 

PhasesData
Phase112
Phase218
Phase314

 

Fo the moment, I get :

 

PhasesData
Phase112
Phase210
Phase28
Phase314

 

with the following code:

 

 

Table2 = 
FILTER(
    UNION(
        SELECTCOLUMNS(Table1;"Phases";"Phase1";"Data";Table1[Phase1]);
        SELECTCOLUMNS(Table1;"Phases";"Phase2";"Data";Table1[Phase2]);
        SELECTCOLUMNS(Table1;"Phases";"Phase3";"Data";Table1[Phase3])
    );[Data]<>blank()
)

 

 

But I don't see how to get only one line for phase2.

If someone has an idea, please share !

 

Regards,

CR

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    CR instead of selectcolumns use summarize

     

    SUMMARIZE( FILTER ( Table, Table[Phase2] <> BLANK()),"Phases", "Phase2", "Data", SUM ( Table[Phase2)))

     

    do it for every phase.

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    CR instead of selectcolumns use summarize

     

    SUMMARIZE( FILTER ( Table, Table[Phase2] <> BLANK()),"Phases", "Phase2", "Data", SUM ( Table[Phase2)))

     

    do it for every phase.

    • CR's avatar
      CR
      Resolver II
      6 years ago

      thanks parry2k 

       

      it works fine !

       

      Table2 = 
UNION(
    SUMMARIZE(Table1;"Phase";"Phase1";"Data";SUM(Table1[Phase1]));
    SUMMARIZE(Table1;"Phase";"Phase2";"Data";SUM(Table1[Phase2]));
    SUMMARIZE(Table1;"Phase";"Phase3";"Data";SUM(Table1[Phase3]))
)