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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

AlB thanks for the reply.

 

Well, thanks for the suggestion, but i cant use the power query,
Because my data model is SSAS, sql server 2016 enterprise.

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

AlB I tried this,

Newtable = 
    SUMMARIZE('Table1','Table1'[Emp ID],
        "Ops Controller",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),'Table1'[Role]="Ops Controller"),
        "Biz HR Manager",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),'Table1'[Role]="Biz HR Manager"),
        "Budget Approver",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),'Table1'[Role]="Budget Approver"),
         "Unit head",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),'Table1'[Role]="Unit head"),
         "Biz HR",CALCULATE(FIRSTNONBLANK('Table1'[Manager],1),'Table1'[Role]="Biz HR")
        )

Output:- 

 

But the values in biz hr are getting only one value.
Any suggestion