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

 

I think you need to use the Pivot/Unpivot features in the query editor for that, rather than DAX

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.

 

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous

    What would the desired output be in your example if  A had as Budget Approvers H and J instead of only H?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Yes. Same issue with Biz Hr also.

      Is it possible to create columns as Biz Hr 1, Biz Hr 2, Biz Hr 3, and values as 

       

      Emp ID  | Biz Hr 1 |Biz Hr 2 |Biz Hr 3

      A             C            D           E

       

       

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Anonymous

        Hmmm, this looks tough to do without looping instructions like the ones you have in other programming languages

        Is Biz Hr the only role that can have more than one manager? Is it always three as in your example or does it vary?

  • 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