Pivot measure based on the pivot row label grouping

[Anonymous]

Hi All

Below is a sample table for which a pivot needs to be created.

Measure:
Err = IF(HASONEVALUE(Range[Group2]),0.11,IF(HASONEVALUE(Range[Group1]),0.03,0.02))

The goal is to create Err as expected but it fails when context for Group1/Group2 gets filtered down to one value. Please fish me out of this problem I've been struggling with from past two days. Thanks in advance.

Thanks

Guru

[amitchandak · Accepted solution]

@Anonymous , Try with isinscope

Measure:
Err = IF(isinscope(Range[Group2]),0.11,IF(isinscope(Range[Group1]),0.03,0.02))

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please try the below measure.

Err =

IF(ISFILTERED(Range[Group2]),0.11, IF(ISFILTERED(Range[Group1]), 0.03, 0.02))

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Amazing!! I've been struggling from past two days and you all solved it like nothing. Is it possible to accept multiple answers as solutions? Have come to know that ISFILTERED can result in false positives hence accepting the reply from @amitchandak as the solution. Hope you understand. Thanks by the way for your quick response.

[amitchandak · Accepted solution]

@Anonymous , Try with isinscope

Measure:
Err = IF(isinscope(Range[Group2]),0.11,IF(isinscope(Range[Group1]),0.03,0.02))

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Anonymous]

You really are a super man. Have searched lot of questions and most of them had your replies. And thanks a lot for the best possible solution. Somehow I'm unable to see ISINSCOPE while creating DAX measure.

[amitchandak]

@Anonymous , Thanks. isfiltered is also driven by slicer values. Isinscope is not.  so depending on the need you can use it.