Conditional Formatting Colours do not work for for Card visualisation

[BOti62]

Hi All

In the screenshot below I have created a Power BI Card visual which changes value based on what I select in the Comparisons Parameter.  

Below is the DAX code which I used:

ColourForComparisonsParameter =

VAR _sel = SELECTEDVALUE(‘Time Parameters’ [Time Parameters Fields])

VAR _var = SWITCH (

TRUE()

, CONTAINSSTRING(_sel, “Yesterday”), [Latest Total Errors] – [Total_Errors_1_Day_Ago]

, CONTAINSSTRING(_sel, “7 Days”), [Latest Total Errors] – [Total_Errors_7_Day_Ago]

, CONTAINSSTRING(_sel, “7 Days”), [Latest Total Errors] – [Total_Errors_30_Day_Ago]

, [Latest Total Errors] – [Total_Errors_365_Day_Ago]

)

RETURN

SWITCH(

TRUE()

, _var < 0, “Green”

, _var > 0, “Red”

, “Black”

)

So the Dax code is saying that if I select a value in the Comparisons parameter e.g Yesterday then the calculation it should do is Latest Total Errors (Measure) - Total Errors 1 Day Ago (Measure) and if this value is greater than 0 then make it red and if less than 0 then make it green. 

However the colours for when I click on vs 7 Days Ago and vs 30 Days Ago dont match and show the opposite of what I want, whilst vs Yesterday and vs Last Year work fine show the right colours. For example 0.02% and 102 should be in green not red.

Is anyone able to help please.

[amitchandak]

@BOti62 , is this calculation used for showing 102 ?

SWITCH (

TRUE()

, CONTAINSSTRING(_sel, “Yesterday”), [Latest Total Errors] – [Total_Errors_1_Day_Ago]

, CONTAINSSTRING(_sel, “7 Days”), [Latest Total Errors] – [Total_Errors_7_Day_Ago]

, CONTAINSSTRING(_sel, “7 Days”), [Latest Total Errors] – [Total_Errors_30_Day_Ago]

, [Latest Total Errors] – [Total_Errors_365_Day_Ago]

)

I doubt some issues with calculation

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[BOti62]

Hi amitchandak

THanks for getting back to me

The below DAX formula is what I used to get 102:

LatestErrors vs 7dayago =

VAR _var = [LatestTotalErrors] – [Total_Errors_7_Day_Ago]

VAR _pctchange = var/[Total_Errors_7_Day_Ago]

VAR _pctFormatted = IF(ABS(_pctchange) >=1, SIGN(_pctchange) * 1, _pctchange)

VAR

    _direction =

         SWITCH(

               TRUE()

             , _var > 0, “🔼”

          , _var < 0, “🔽”

          , _var “⏺️”

)

RETURN

_direction & FORMAT(_pctchange, “##0.0##%”) & “ | “ & _direction & FORMAT(_var, “0”)

The below measure is what I used within the above measure for LatestTotalErrors.

LatestTotalErrors =

VAR LastDateStamp = CALCULATETABLE(LASTNONBLABK(‘Calender’[Date],0),ALL (‘DateQualityDashboard’) )

RETURN (

CALCULATE (SUM (‘DataQualityDashboard’[ErrorCount]),

KEEPFILTERS (LastDateStamp ) ) + 0)

Below is some sample data

Date	Error Count
11-Jan-24	324
11-Jan-24	414
04-Jan-24	324
04-Jan-24	363