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
Super UserHey 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!
- TomMartens
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