Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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...
Solved! Go to 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 , 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
@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!
and hovering for more details
So, I had the system find the Direct Service table
But that results in the following: The MAX function only accepts a column reference as an argument
So, I used the Date field in the Direct Service Table and the formula was accepted without errors!
And the Table is now conditionally formatting!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |