topic Re: Conditional formatting via isinscope() in DAX Commands and Tips

Conditional formatting via isinscope()

Anonymous — Wed, 09 Nov 2022 09:28:43 GMT

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

Re: Conditional formatting via isinscope()

Anonymous — Tue, 08 Nov 2022 18:23:37 GMT

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

Re: Conditional formatting via isinscope()

Anonymous — Wed, 09 Nov 2022 07:03:09 GMT

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.

Re: Conditional formatting via isinscope()

Anonymous — Wed, 09 Nov 2022 10:39:02 GMT

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.

Re: Conditional formatting via isinscope()

Anonymous — Thu, 10 Nov 2022 02:44:20 GMT

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.

Re: Conditional formatting via isinscope()

Anonymous — Thu, 10 Nov 2022 07:59:42 GMT

Hi Anonymous,

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:

Re: Conditional formatting via isinscope()

Anonymous — Thu, 10 Nov 2022 10:01:32 GMT

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.

Re: Conditional formatting via isinscope()

Anonymous — Fri, 11 Nov 2022 13:19:38 GMT

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.

Re: Conditional formatting via isinscope()

Anonymous — Fri, 18 Nov 2022 10:57:07 GMT

Hi Anonymous ,

Do you have any idea?

Re: Conditional formatting via isinscope()

Anonymous — Mon, 21 Nov 2022 07:50:06 GMT

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.