Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Change Row totals for matrix based on the drill down

Hello all, good morning.

 

Wanted to know how can I change the value of a measure based on if the matrix is opened into the lowest category or if it is showing as all summed up.

 

Right now it shows like this:

Pedro_Okazaki_0-1652186407295.png

 

What I need is, the column Service Status (All Invoices Table) should be the Overall Status for the first row and for each category, it should show the status for the category (Service Invoice Status).

 

I tried using hasonevalue but id did not work,

Service Status (All Invoices Table) =
IF(HASONEVALUE('All Invoices Table'[Service Invoice Status]),LASTNONBLANK('All Invoices Table'[Service Invoice Status],'All Invoices Table'[Service Invoice Status]),LASTNONBLANK('All Invoices Table'[Overall Status],'All Invoices Table'[Overall Status]))

Is this possible?

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@Anonymous the function you need is: ISINSCOPE.
Check out this tutorial:
https://www.youtube.com/watch?v=1tdufLz7_0U
and then this video for distinguishing HASONEVALUE from ISINSCOPE.
https://www.youtube.com/watch?v=8UNNo233eFc


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

3 REPLIES 3
SpartaBI
Community Champion
Community Champion

@Anonymous the function you need is: ISINSCOPE.
Check out this tutorial:
https://www.youtube.com/watch?v=1tdufLz7_0U
and then this video for distinguishing HASONEVALUE from ISINSCOPE.
https://www.youtube.com/watch?v=8UNNo233eFc


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Anonymous
Not applicable

Thank you very much, it worked!

 

Just writing down if anyone else needs this for reference, the below was my final dax measure:

 

Service Status (All Invoices Table) =


SWITCH(TRUE(),ISINSCOPE('All Invoices Table'[Service]),LASTNONBLANK('All Invoices Table'[Service Invoice Status],'All Invoices Table'[Service Invoice Status]),ISINSCOPE('All Invoices Table'[Release Reference]),LASTNONBLANK('All Invoices Table'[Overall Status],'All Invoices Table'[Overall Status]))

 

@Anonymous my pleasure 🙂
I just saw that the link for the other video slipped so I added it now to the message.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.