Search code examples
datetimepowerbiconditional-formatting

Conditional Format based on dates in Power BI

I am trying to get a date/time in Power BI to conditionally format if it exceeds a certain date.

I found a formula that supposedly I could use to get the conditional formatting to work but the formula doesn't seem to work for me some reason; I am not familiar with Power Bi syntax.

I want the dispatch data to highlight if it passes the critical dispatch time.

enter image description here

I found the following as an example of what someone did to get their dates to conditionally format but I wasn't able to get their formula to work for me.

enter image description here

enter image description here

Here is what I tried doing in my power query:

enter image description here

Just for context, Leg 1 Dispatch = Dispatch in my columns. I know this must be some sort of syntax issue but I can't seem to find a resource that helps me identify the issue.

Here is are the columns that I am trying to use, and the suggested formula that doesn't seem to 'find' those columns. enter image description here

Solution

  • Yep, you were right on with creating a measure to calculate the difference and formatting based on the measure.

    Your immediate error (Token Eof expected.) is just saying your parenthesis don't match. In this case, you have a ) after [#"Critical Dispatch Time (Local)"], but no preceding function or (.

    It's unclear from the question, but if you are creating a custom column that only references columns in the same table, the MAX() function is not needed. The purpose of the MAX() function in the tutorial was to aggregate and consolidate multiple values to a single value if needed. However, in a custom column on the same table as the dates you are referencing, the immediate context is just the current row. Hence, you can simply write:

    IF( [Leg 1 Dispatch Date (Local)] > [Critical Dispatch Time (Local)], 1, 0 )

    If you are writing a measure that can be run over any context, then it is necessary to properly handle the potential for multiple values. We can just tweak it a little using the SELECTEDVALUE() function, which only returns the value when the there is a single distinct result (otherwise it returns blank or another user-defined expression).

    IF( 
    SELECTEDVALUE([Leg 1 Dispatch Date (Local)] ) 
        > SELECTEDVALUE([Critical Dispatch Time (Local)] ), 
    1, 0
)

    Either a custom column or a custom measure will work for this, but a measure is slightly preferable on a large dataset because it is only computed if needed.

    Either way you choose (measure/column), just set your rule formatting to only trigger when the expression is 1.