DAX Query With Switch & IF Condition Misbehaves - Power BI

[Anonymous]

I tried with below DAX queries to control the color of a rectangle shape. Both the queries are making the rectangle white color for blank values and other some values too. What would be the reason and solution ?

USING IF ---------

COSTIndicatorColor =

IF (

[COSTMeasure] < -15, "#FF0000",

IF (

AND (

[COSTMeasure] >= -15,

[COSTMeasure] < -5

), "#EBAE3E",

IF ( [COSTMeasure] > -5, "#33AF81", "#33AF81" )))

USING Switch -----

COSTIndicatorColor =
var __amount = [COSTMeasure]
return
SWITCH( TRUE()
, __amount < -15 , "#FF0000"
, __amount >= -15 && __amount < -5, "#EBAE3E"
, __amount >= -5 , "#33AF81"
, "#33AF81"
)

Formula used for CostMeasure field which has been used inside - 

COSTMeasure = SUM(Invoices[TOTAL_AMOUNT]) - SUM(PlannedPayment[PlannedPayment]) + 0

[Anonymous]

Hi @Anonymous ,

Which visual you were trying to use conditional formatting for? Currently only table or matrix support use conditional formatting. How did you set the conditional formatting? You can review the content in the following documentation for the details of conditional formatting.

Use conditional formatting in tables

Best Regards

[selimovd]

Hello @Anonymous ,

check the values first with a table to see if the right values are produced.

Then you can change it to colors. This makes it easier to understand what is really going on.

If you want to provide the file I can also take a look where the error is.

Best regards

Denis

[Anonymous]

@selimovd  I have tried giving in a table. The color values misbehaves when there are multiple values exists. Could you please help.

Please find the power BI file in link https://1drv.ms/u/s!Ah-kO6CqeTNOgh8lcsQahymsIZj_?e=BSuXDf 

When you filter the Project UID, for some projects the color becomes white which has multiple values. In multiple value cases, one value has color indicator as blank and I need to avoid it or take any of the fields which has color.

[selimovd]

Hey @Anonymous ,

there are a few issues with your report.

Your slicer is filtering on the column 'pjrep MSP_EpmProject_UserView'[ProjectUID] that has 491 IDs.

You added the column with the color to the column 'PlannedPayment'[ProjectUID] that has 154 IDs.

That means you just defined a color for 154 IDs and in the report you can filter 491 IDs. So at least 337 IDs that you chose from the list don't have a color defined. For these colors the background will stay white.

For the color you chose the first value of the column COSTIndicatorColor. So if you have multiple values selected as you mentioned a random/the first color in the current selection will be chosen.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[selimovd]

Hey @Anonymous ,

could you fix your issues with the information from my last post?

I'm curious if you could solve this issue 🙂

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi