Forum Discussion
Conditional formating based on a relation to other datasources
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
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.
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
(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!
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