Reply
Highlighted
Member
Posts: 277
Registered: ‎02-14-2017
Accepted Solution

DAX: Pivot table with multiple values.

[ Edited ]

I have a table as below

 

Emp ID   |Managers  |Roles

ABUnit head
ACBiz HR
ADBiz HR
AEBiz HR
AFOps Controller
AGBiz HR Manager
AHBudget Approver

 

 

Using DAX how can we pivot this table as below output

 

Emp ID |Unit Head |Biz HR 1 |Biz HR 2 |Biz HR 3|Ops Controller |Biz HR Manager |Budget Appover

 

A          B                C             D             E             F                       G                         H

Any help please 

 

Mohan V


Accepted Solutions
Member
Posts: 277
Registered: ‎02-14-2017

Re: DAX: Pivot table with multiple values.

@AlB I got the solution buddy,

To get the ranks i tried the below dax,

 

Capture1.JPG

 

And then i pivoted the table using below dax,

 

Capture1.JPG

 

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

View solution in original post


All Replies
AlB Super Contributor
Super Contributor
Posts: 779
Registered: ‎11-12-2018

Re: DAX: Pivot table with multiple values.

Hi @MohanV

 

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

Member
Posts: 277
Registered: ‎02-14-2017

Re: DAX: Pivot table with multiple values.

@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 Super Contributor
Super Contributor
Posts: 779
Registered: ‎11-12-2018

Re: DAX: Pivot table with multiple values.

Hi @MohanV

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

Member
Posts: 277
Registered: ‎02-14-2017

Re: DAX: Pivot table with multiple values.

@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:- 
Capture.JPG

 

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

Member
Posts: 277
Registered: ‎02-14-2017

Re: DAX: Pivot table with multiple values.

[ Edited ]

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 Super Contributor
Super Contributor
Posts: 779
Registered: ‎11-12-2018

Re: DAX: Pivot table with multiple values.

@MohanV

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?

Member
Posts: 277
Registered: ‎02-14-2017

Re: DAX: Pivot table with multiple values.

@AlB Yes, This may vary, as for each employee there can be any no of Biz hrs, Unit Heads, Ops Controller etc from 1 to n.

Can it be done in this way as,

 

I have created a column as Role Values using Switch, which i am defining values to each role.

Role Values = SWITCH(TRUE(),
Table1[Role]="Unit head",1,
Table1[Role]="Biz HR",2,
Table1[Role]="Ops Controller",3,
Table1[Role]="Biz HR Manager",4,
Table1[Role]="Budget Approver",5)

Then i am creating a column which will define ranks based on this column for each employee.
I tried the below dax.

Ranks = 
    RANKX(
        'Table1',
        'Table1'[Role Values],
        ,
        ASC,
        Dense
        )

But getting output as below,

Capture1.JPG

 

Can it be like

 

Emp ID   |Manager |Role                     |RoleValues |Ranks

ABUnit head11
ACBiz HR21
ADBiz HR22
AEBiz HR23
AFOps Controller31
AGBiz HR Manager41
AHBudget Approver51

 

By using these ranks, i can pivot the table even though if i have multiple id's for each role.

 

Is it possible. Any suggestion??

 

Regards,

Mohan V

Member
Posts: 277
Registered: ‎02-14-2017

Re: DAX: Pivot table with multiple values.

@AlB Updated DAX for Ranks,

CALCULATE(COUNT(Table1[Role]),
        FILTER(ALLEXCEPT(Table1,Table1[Role],Table1[Emp ID]),
        Table1[Role]<=EARLIER(Table1[Role]
        )))

Output:
Capture1.JPG

AlB Super Contributor
Super Contributor
Posts: 779
Registered: ‎11-12-2018

Re: DAX: Pivot table with multiple values.

Hi @MohanV

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]
)