Conditional Formatting Based On Multiple Crieria (sorry I searched, but couldn't find solution)

[Atanas_Atanasov]

Dear community,

I have a simple table loaded in power BI which looks like that (screenshot 1)

The metric column and the quarter columns are coming from a single table named "Quarterly Results" - Quarterly Results[metric] and Quarterly Results[Quarter].

The measure you are seeing is extremely simple: 

Volumes =

    SUM( 'Quarterly Volumes'[Value] )

When I put it in the values section of the matrix the metric and the quarters are slicing it.

Now, here's my problem. I must create color coding which colors in red the quarter where 

BF Surveyable Events > 7 and BF Surveys = 0.

As you can see, that would be FY25Q3 as marked here:

I've tried multiple solutions and none of them worked. 

I also created 2 new measures:

BF Surveyable Events =

CALCULATE(

    [Volumes],

    KEEPFILTERS( 'Quarterly Volumes'[Metric] = "BF Surveyable Events" )

)

and 

BF Surveys =

CALCULATE(

    [Volumes],

    KEEPFILTERS( 'Quarterly Volumes'[Metric] = "BF Surveys" )

)

Then created a new measure which has the following syntax:

Check =

VAR calc_1 = [BF Surveyable Events]

VAR calc_2 = [BF Surveys]

RETURN

SWITCH(

    TRUE(),

    MIN( 'Quarterly Volumes'[Metric] ) = "BF Surveyable Events" &&

    calc_1 > 7 &&

    calc_2 = 0,

    1,

    0

)

Thinking to use it as a rule in my conditional formatting. So when it equals 1 to be red, otherwise black.

But what it does its returning 1 to all values for BF Surveyable Events like here:

And respectively, it colors my whole line in red. While I'd expect it to color only FY25Q3.

Can anyone help me with that please? 

All due respect,

Atanas

[Atanas_Atanasov]

@divyed and @Praful_Potphode - also I was able to do it with DAX today... there was sorting happening in the table. The metrics column was sorted by another column, so the order appears the same every time. So, basically:
CALCULATE(
    [Volumes],
    REMOVEFILTERS( Quarterly Volumes[Order] ),
    Quartertly Volumes[Metric] = "BF Surveyable Events"
) works like a charm now... I know it is a trivial problem, but really was not aware of it.
Biggest takeaway is if your DAX is not working as it should be always check that... mainly in calendar tables. 
Thank you both,
Atanas

[divyed]

Hello @Atanas_Atanasov ,

Visual calculation is definately an option here but you can also modify your dax to do the same. I can see issue in your dax which has potential conatext issue. I would suggest to use selectedvalue function to get current values and that will work. You can share sample raw data if you want full dax.

I hope this helps.

Cheers.

Did I answer your query, Mark this as solution if this helps, kudos are appreciated.

Cheers

Neeraj Kumar

[Atanas_Atanasov]

@divyed - thank you for the suggestion! As @Praful_Potphode suggested I used visual calculation. I also resolved it with DAX and disconnected table. I think something might not be right with the report in general as I inherited it from another developer. Thank you one more time and wish you all the best,

Atanas

[Praful_Potphode · Accepted solution]

Hi @Atanas_Atanasov ,

You can use Visual Calculation(Select Your Visual and click on New Visual Calculation) to achieve the result shown below:

PFA PBIX.

Please give Kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Atanas_Atanasov]

@Praful_Potphode - Thank you so much! That resolved my issue. I have also accomplished the coloring with disconnected table where i just put the values of the metric column. Then dragged it into the matrix and applied the same code:

if( metric name = "BF Surveyable Events" && [BF Surveyable Events] > 7 && [BF Surveys] = 0, 1, 0 )
But this works only with disconnected table. However, your solution is simpler and more elegant. Thank you one more time.