Conditional formatting via isinscope()

[J4ns3n]

Hi All,

Via a measure and the function isinscope() I assigned conditinal formatting on 3 different hierarchical levels (matrix rows). This working just fine, but the business wants to have a button where they can filter based on the numbers which are red. 

The problem is now that when the third hierarchical level is red a filter on the color code does not return all 3 hierarchical levels. Is there a function where you can overwrite these levels? So you can say that when isinscope(Level 2) level 1 is returend, but also level 3. 

Measure conditonal formatting:

IF (ISINSCOPE(hierarchical [level 3]),blank(),

IF (ISINSCOPE(hierarchical [level 2])&&([Difference %])>varLevel2%),"#ff2000",

IF (ISINSCOPE(hierarchical [level 1])&&([Difference %])>varLevel1%),"#ff2000")))

The sample PBIX is on this wetransfer link: https://we.tl/t-9TvD36eFPP

[v-rongtiep-msft]

Hi @J4ns3n ,

Please create 2 tables.

And then create a measure.

Measure2 =
VAR a =
    IF (
        ISINSCOPE ( 'Sample'[Level 2] )
            && NOT ( ISINSCOPE ( 'Sample'[Level 3] ) )
                && SELECTEDVALUE ( 'Sample'[Level 2] ) IN VALUES ( level2[Level 2] ),
        1,
        0
    )
VAR b =
    IF (
        ISINSCOPE ( 'Sample'[Level 3] )
            && SELECTEDVALUE ( 'Sample'[Level 3] ) IN VALUES ( level3[Level 3] ),
        1,
        0
    )
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( level2[Level 2] ) && NOT ( ISFILTERED ( level3[Level 3] ) ), a,
        ISFILTERED ( level3[Level 3] ) && NOT ( ISFILTERED ( level2[Level 2] ) ), b,
        ISFILTERED ( level2[Level 2] ) || ISFILTERED ( level3[Level 3] ), a + b
    )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[J4ns3n]

Hi @v-rongtiep-msft ,

This already helps and I think we are going the right way, but we are not there yet. 

In the PBIX you created two different slicers which control the matrix. But I want to see the next thing: 

I want 1 slicer for all three hierachies. So when 1 hierachy is colored red (based on the conditional formatting measure) via the filter the whole 3 levels are shown.

So without the slicer/filter it shows this:

The conditional formatting is right, these numbers should be show in red. 
After the slicer/filter I want to see everything of the countries (level 1) which are red or have in the lower levels/hierachy a red number. This means that the slicer/filter should exclude every country which have only black (normal) numbers. This should all happen based on the red numbers, which are based on a measure. 

[v-rongtiep-msft]

Hi @J4ns3n ,

I cannot reproduce the issue. Could you please provide some sample data without privacy information and desired output with more details.

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[J4ns3n]

Hi @v-rongtiep-msft , 

I posted a PBIX file on wetransfer: https://we.tl/t-9TvD36eFPP

So without the filter the matrix looks like this:

After the filtering/button it should look like this: so based if a inscope() function matches, every hierachal level should be visible:

In this example based on the second hierachy the numbers are turned red. 

[v-rongtiep-msft]

Hi @J4ns3n ,

There is nothing error with the formula. 

MEASURE conditonalformatting =
    VAR varLevel2 = ( 1 / 100 )
    VAR varLevel1 = ( 35 / 1000 )
    RETURN
        IF (
            ISINSCOPE ( 'Sample'[Level 3] ),
            BLANK (),
            IF (
                ISINSCOPE ( 'Sample'[Level 2] )
                    && ( [Difference%] ) > varLevel2,
                "#ff2000",
                IF (
                    ISINSCOPE ( 'Sample'[Level 1] )
                        && ( [Difference%] ) > varLevel1,
                    "#ff2000"
                )
            )
        )

In your sample data, the varlevel11 value is 0.035(3.5%).

 There is no data bigger than 3.5%.

I have change 35 to 25.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[J4ns3n]

Hi @v-rongtiep-msft, 

There was nothing wrong with the formula. I was looking at a way where I can filter based on this formula. 

So when 1 hierachy is matched, I want to see all the details from level 1 to level 3 of a certain country for example, NL.

So in this example hierachy level 2 is matched for the countries NL and DE. Therefore I want to see all the details on that level:

[J4ns3n]

Hi @v-rongtiep-msft ,

Do you have any idea? 

[v-rongtiep-msft · Accepted solution]

Hi @J4ns3n ,

What is your desired output?  I'm a little confused. When we select the level 1, we can see level 2 and level 3. But when we choose level 3,  Its upper level cannot be hidden.

If so, you can vote here: Microsoft Idea (powerbi.com)

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[J4ns3n]

Other functions which can evaluate each hierachie and can create the right colors are also welcome.