Conditional formating based on a relation to other datasources

[MakingBreaddata]

Hey there,

i want to highline the data in my matrix which is out of the specification:

for example the weight for the article 8956 (formating of the column is text) should be between 125 - 128. In my case it is 124 so it has to be marked with a red backround. I tried the standard conditional formating but it did not work, it is easy to made in excel so it should also work in Power bi. Anyone an idea?

regards

[TomMartens]

Hey @MakingBreaddata ,

I'm wondering why it's not possible to use Rules for the conditional formatting like so:

The result:

Here you will find the article that explains the conditional formatting: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

Hopefully, this provides what you are looking for.

Regards,

Tom

[MakingBreaddata]

Hey @TomMartens , 

but how can i set up a specific Rule for each article. In your example you set up a range between 200 - 250 $ for every article. But i want to set up a different range for every article. In your table this would mean that the range for 23012961 is between 200 - 250 $ (it gets an yellow backround) and for 23012962 the range should be between 180 - 200 $ (the backround is still blank, it would dye when the value is between 180 - 200 $).

or formulated as a rule: if the line in the coulumn OnlineSalesKey is 23012961, all values between 200 - 250  should be marked; if the line in the coulumn OnlineSalesKey is 23012961 all values betwenn 180 - 200 should be marked; and so on for every article.

Hope this is understandable. Thank you in advance.

[TomMartens]

Hey @MakingBreaddata ,

you must provide a table containing the min and max value per article, I assume this table is called rangeTable and has the columns: Article | lowerbound | upperbound.

Then you can create a measure  that returns the backglround color like so:

vizAid bgc Value = 
var currentArticle = SELECTEDVALUE( '<dimTable>'[Article] )
var currentValue = [a measure returning the value you want to check]
var lowerbound = CALCULATE( MIN( 'rangeTable'[lowerbound] ) , '<dimTable>'[Article] = currentArticle )
var upperbound = CALCULATE( MAX( 'rangeTable'[upperbound] ) , '<dimTable>'[Article] = currentArticle )
return
IF( currentValue >= lowerbound && currentValue <= upperbound
    , "red"
    , BLANK()
)

Make sure the measure is of data type string.

Choose the conditional formatting option Field value and select the measure.

Hopefully, this provides what you are looking for.

Regards,

Tom

[MakingBreaddata]

(English below)

Hallo Tom,

was ist mit 

var currentValue = [a measure returning the value you want to check]

gemeint, könntest du das an einem Beispiel festmachen? Und wo gebe ich das Measure ein ("Choose the conditional formatting option Field value and select the measure.") Vielen Dank im Voraus.

English:

Hello Tom,

what do you mean with 

var currentValue = [a measure returning the value you want to check]

could you give me an example for that? Where should i have to put in the measure ("Choose the conditional formatting option Field value and select the measure.") ? Thank you in advance!

[TomMartens · Accepted solution]

Hey @MakingBreaddata ,

the matrix visual shows values for different numeric columns or measures like "Summe von Weight". Without knowing the exact "source" of the values it's difficult to provide 100% exact guidance. 

However,  if the numeric expression is "coming" from a column (my assumption) then you can replace the line

var currentValue = [a measure returning the value you want to check]

with this line

var currentValue = SUM( 'FactOnlineSales'[SalesAmount] )

Please keep in mind that creating explicit measures instead of using implicit measures is considered a best practice (one of the many readings: https://towardsdatascience.com/understanding-explicit-vs-implicit-measures-in-power-bi-e35b578808ca).

After you created the measure that returns the "colorname" or a color hexcode as a string "#eb7134" you have to select the measure for the color coding. Make sure that the data tpye of the measure is "text." If this is not the case it can not be selected in a later step:

Here are the steps to select the measure for the color coding:

• Mark the table or matrix visual
• Enable the conditional formatting in the formatting pane, for matrix visuals you will find this on the Cell elements  card
  
• Choose the "Field value" format style and select the measure
  

Hopefully this provides what you are looking for.

Regards,

Tom