12-06-2018 04:05 AM - edited 12-06-2018 04:59 AM
I have a table as below
Emp ID |Managers |Roles
|A||G||Biz HR Manager|
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
Solved! Go to Solution.
12-06-2018 07:41 AM
@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
12-06-2018 04:52 AM
@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") )
But the values in biz hr are getting only one value.
12-06-2018 04:55 AM - edited 12-06-2018 04:56 AM
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
12-06-2018 06:14 AM
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?
12-06-2018 06:33 AM
@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,
Can it be like
Emp ID |Manager |Role |RoleValues |Ranks
|A||G||Biz HR Manager||4||1|
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??
12-06-2018 07:29 AM
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] )