cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MohanV Established Member
Established Member

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

Super User
Super User

Re: DAX: Pivot table with multiple values.

@MohanV

I guess I have to defend my reputation here Smiley Very HappySmiley Very 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 

 

 

image.png

Highlighted
MohanV Established Member
Established Member

Re: DAX: Pivot table with multiple values.

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 284 members 3,110 guests
Please welcome our newest community members: