Forum Discussion

MakingBreaddata's avatar
MakingBreaddata
Regular Visitor
3 years ago
Solved

Conditional formating based on a relation to other datasources

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

 

 

Need Help
Show and Tell
  • TomMartens's avatar
    TomMartens
    3 years ago

    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

     

5 Replies

    • MakingBreaddata's avatar
      MakingBreaddata
      Regular Visitor
      3 years ago

      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's avatar
        TomMartens
        Super User
        3 years ago

        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