Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Create Measure for Conditional Formatting Categorical values

Hi there, 

 

I have a categorical column with three values: On Time, Late, or Blank. I am trying to apply condition formatting to the x-axis (date) for a chart. I need a measure to return red if value Late, blue if value is On Time, and grey if value is blank for the corresponding dates on the chart. Is there an example somewhere of how to do this?

 

So far I tried to create a measure to bin the categories into numbers. 

 

Ontime_Number =
VAR OnTime = SELECTEDVALUE(FlaggingLabor[On Time])
RETURN

SWITCH(TRUE(),
OnTime = "On Time", 1,
OnTime = "Late", 2,
ISBLANK(OnTime),3
)
 
Then I tried to apply conditional formatting on the x-axis of the clustered bar chart. 

For some reason when I do this, the formatting only reads the 3, as if all records were blank but that is not the case when viewing the measure in a table. 

 

Any help is greatly appreciated. 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Create a color measure and use that in conditional formating using field value option

     

    Ontime_Number =
    VAR OnTime = max(FlaggingLabor[On Time])
    RETURN

    SWITCH(TRUE(),
    OnTime = "On Time", "Blue",
    OnTime = "Late", "Red",
    ISBLANK(OnTime),"Green"
    )

     

    How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak ,

       

      Thanks for your response. When I implement your suggestion, all of the dates on the x-axis still show as one color or "On Time". I think what is happening, is that on some dates have multiple values for the field On Time. In this case, I would want to have the date show red if any record is late on that date. Is this possible? 

       

      Thanks 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, Anonymous 

     

    You can count three conditions and then choose colors according to your needs (Prioritize late and blank conditions).

    Like this:

    Measure =
//VAR ontime =   COUNTX ( FILTER ( 'Table', [On Time] = "On Time" ), [On Time] )
VAR late =
    COUNTX ( FILTER ( 'Table', [On Time] = "Late" ), [On Time] )
VAR blankontime =
    COUNTX ( FILTER ( 'Table', [On Time] = BLANK () ), [On Time] )
RETURN
    IF ( late <> 0, "Red", IF ( blankontime <> 0, "Grey", "Blue" ) )

    Select 'field value' in conditional format.

    Like this:

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-janeyg-msft ,

       

      Thank you for your response. I applied the countx function to the measure as you've shown, but the result in my visual is that if any record is late, all dates show red. 

      When I filter the late record out with a slicer, they all go blue. 

      I am needing the colors to not be uniform, but indicate per date if there was a late record on that date. The result would be some dates as blue and some dates as red. Let me know if you have any further thoughts. 

       

      Thank you! 

       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        4 years ago

        Hi, Anonymous 

         

        I am not clear what visual you are using and in which option the conditional formatting is used. Can you explain it?

        It's fine with me.

        Can you share a sample file? So we can see your problem intuitively.

         

        Best Regards,
        Community Support Team _ Janey