Forum Discussion
DAX: Pivot table with multiple values.
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 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 | B | Unit head | 1 | 1 |
| A | C | Biz HR | 2 | 1 |
| A | D | Biz HR | 2 | 2 |
| A | E | Biz HR | 2 | 3 |
| A | F | Ops Controller | 3 | 1 |
| A | G | Biz HR Manager | 4 | 1 |
| A | H | Budget Approver | 5 | 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??
Regards,
Mohan V
AlB Updated DAX for Ranks,
CALCULATE(COUNT(Table1[Role]),
FILTER(ALLEXCEPT(Table1,Table1[Role],Table1[Emp ID]),
Table1[Role]<=EARLIER(Table1[Role]
)))Output:
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] )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
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
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)) )
