Conditionally Formatting based on "latest" related record

[JDBOS]

We are a social services organization helping people that need support.

In a Table, we need to highlight the background of a cell for the date of Last Contact (Direct Service)

>> Yellow if more than 25-39 Days in the past, 

>> Red if more than 39 Days in the past.

Here's a screenshot of the current table

Direct Service for Case Record

We've been able to do conditional formatting if the date is on the Case Record but we're having trouble when it's a look-up to the latest record in a related table (in this case Latest 'Direct Service'[ExpECM_Date__c])  Case Records can have many Direct Service entries and are related using the Case Record ID.

I'm assuming we need to build a measure that calculates the "days since the last direct service" for each Case Record ID which we can then use for conditional formatting.

Something like...

days since last dir svc = TODAY()-maxx('Direct Service','Direct Service'[ExpECM__Case_Record__c]=max('Case Record'[id]))

As a novice with formulas, I'd really appreciate your coaching! 

And how would it differ if we build in PBI Desktop vs in Power Query Editor.

Thanks in advance!!

[amitchandak · Accepted solution]

@JDBOS , create a measure like this

measure =
var _max = Today() //maxx(allselected(Table),Table[Direct Service]) //Or use masx date
var _diff =datediff(max(Table[Direct Service]),_max,Day)
return
switch( True() ,
_diff>39 , "Red",
_diff>25 , "yellow",
"white")

And use it in conditional formatting with the "Field Value" option

refer: https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[JDBOS]

@amitchandak thx so much for your quick response - from the final screenshot in this stream, it's now working !! 👍😀

So trying to understand the formula

You created a variable named "_max" as Today

Then created a variable named "_diff"  equal to DATEDIFF {"count of interval boundaries crossed between two dates"} between the max of the Direct Svc Date in the Direct Svc Table and _max {today}, using Days as the interval parameter

then switched based on the value of _diff

Nice formula!

Syntax Error Msg

and hovering for more details

Syntax Error Details

So, I had the system find the Direct Service table

Direct Svc Table as Selection

But that results in the following:   The MAX function only accepts a column reference as an argument

MAX function error

So, I used the Date field in the Direct Service Table and the formula was accepted without errors!

Formula Accepted

And the Table is now conditionally formatting!!

Successful Conditionally formatting