Change Row totals for matrix based on the drill down

[Anonymous]

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:

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?

[SpartaBI · Accepted solution]

@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

Showcase Report – Contoso By SpartaBI

     

[Anonymous]

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

[SpartaBI]

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