Forum Discussion
Conditional Formatting Colours do not work for for Card visualisation
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.
2 Replies
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.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
