Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional formatting via isinscope()

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

Conditional Formatting
Filter using a measure
isinscope
matrix
measure
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    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.

     

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous , 

      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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous ,

      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.