Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX: Pivot table with multiple values.

I have a table as below   Emp ID   |Managers  |Roles A B Unit head A C Biz HR A D Biz HR A E Biz HR A F Ops Controller A G Biz HR Manager A H Budget Approver ...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    AlB I got the solution buddy,

    To get the ranks i tried the below dax,

     

     

    And then i pivoted the table using below dax,

     

     

    The only issue with this workaround is, each time i have manually add the column for the n no of emp id's

     

    And i tried your dax, sorry, it didnt worked, the Ranks2 column is what the output in above image 

     

    Thanks,

    Mohan V

AlB's avatar
AlB
Community Champion
7 years ago

Hi Anonymous

But your last update does not yield what you were looking for, does it?

Try the following. I am curious as to what the next step is. Let me know

 

Table1[Ranks] =
RANKX (
    CALCULATETABLE ( 'Table1'; ALL ( 'Table1'[Managers] ) );
    'Table1'[Managers];
    ;
    ASC
)

Or this, which gets you the same but with 3,2,1 for Biz HR as shown instead of 1,2,3 (I guess it does not matter)

 

Table1[Ranks] =
RANKX (
    CALCULATETABLE ( 'Table1'; ALL ( 'Table1'[Managers] ) );
    'Table1'[Managers]
)

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

AlB I got the solution buddy,

To get the ranks i tried the below dax,

 

 

And then i pivoted the table using below dax,

 

 

The only issue with this workaround is, each time i have manually add the column for the n no of emp id's

 

And i tried your dax, sorry, it didnt worked, the Ranks2 column is what the output in above image 

 

Thanks,

Mohan V

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Anonymous

    I guess I have to defend my reputation here :smileyvery-happy::smileyvery-happy:

    My DAX works. I tested it. I think it doesn't work on your test because you have the column you added, Ranks, in the table and that affects the context transition that my code relies on. If you remove your Ranks columns it should work.

    In any case, can you please post the final DAX code for the pivoting  in text rather than on an image? I'd like to look a bit further into it

    Best 

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi AlB

    Here is the dax,

     

    Newtable = 
    SUMMARIZE('Table1','Table1'[Emp ID],
        "Ops Controller1",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),FILTER(Table1,'Table1'[Role]="Ops Controller" && Table1[Ranks]=1)),
        "Ops Controller2",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),FILTER(Table1,'Table1'[Role]="Ops Controller" && Table1[Ranks]=2)),
        "Biz HR Manager1",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),FILTER(Table1,'Table1'[Role]="Biz HR Manager" && Table1[Ranks]=1)),
        "Biz HR Manager2",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),FILTER(Table1,'Table1'[Role]="Biz HR Manager" && Table1[Ranks]=2)),
        "Budget Approver",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),'Table1'[Role]="Budget Approver"),
         "Unit head",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),'Table1'[Role]="Unit head"),
         "Biz HR1",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),FILTER(Table1,'Table1'[Role]="Biz HR" && Table1[Ranks]=1)),
         "Biz HR2",CALCULATE(FIRSTNONBLANK('Table1'[Manager],2),FILTER(Table1,'Table1'[Role]="Biz HR" && Table1[Ranks]=2)),
         "Biz HR3",CALCULATE(FIRSTNONBLANK('Table1'[Manager],2),FILTER(Table1,'Table1'[Role]="Biz HR" && Table1[Ranks]=3))
        )