cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JDBOS
Helper III
Helper III

Conditionally Formatting based on "latest" related record

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 RecordDirect 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!!

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@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 MsgSyntax Error Msg

 

and hovering for more details

Syntax Error DetailsSyntax Error Details

So, I had the system find the Direct Service table

Direct Svc Table as SelectionDirect Svc Table as Selection

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

MAX function errorMAX function error

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

Formula AcceptedFormula Accepted

And the Table is now conditionally formatting!!

Successful Conditionally formattingSuccessful Conditionally formatting

 

 

 

 

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors