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.
I am trying to make a RAG traffic light indicator that shows when a particular Contract was last updated with new information (within the last week = green, within last two weeks = amber, over two weeks ago = red).
To do this I am using a field value to dictate the fill colour of a circle. It works, however it always shows green as it takes the most recent entry from the whole dataset, ignoring the Slicer that selects one Contract at a time.
This Filter function turns the Slicer's current selection into a value.
Contract select = SELECTEDVALUE(Revised[Contract])
How do I include it within the following traffic light function?
Contract RAG lights =
var _dt = datediff(TODAY(), max(Revised[Week Ending]), day)
return
Switch( TRUE(),
_dt <= 7, "light green",
_dt <= 14, "orange",
_dt >= 15, "red",
"blue")
I tried inserting this line into the above but had no success.
FILTER(Revised, Revised[Contract] = [Contract select])
Thanks for your help.
Solved! Go to Solution.
Hi @JB ,
Try including ALL here:
Proud to be a Super User!
Hi,
Try something like this:
Contract RAG lights =
var _dt = CALCULATE(datediff(TODAY(), max(Revised[Week Ending]), day),ALL(Revised),Revised[Contract]=[Selected contract])
return
Switch( TRUE(),
_dt <= 7, "light green",
_dt <= 14, "orange",
_dt >= 15, "red",
"blue")
Proud to be a Super User!
Hi, thank you for your reply.
I did that and got the following error message about the Switch function:
A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Any thoughts?
@JB
This happens when a measure is placed to a filter. So instead of [Selected contract] you could try using Revised[contract] = SELECTEDVALUE(Revised[contract]) directly in the calculate.
Proud to be a Super User!
Thanks for your response.
The following does not produce any error messages, but it also does not change the colours according to the Slicer; the original problem persists.
Contract RAG lights =
var _dt = CALCULATE(datediff(TODAY(), max(Revised[Week Ending]), day), Revised[contract] = SELECTEDVALUE(Revised[contract]))
return
Switch( TRUE(),
_dt <= 7, "light green",
_dt <= 14, "orange",
_dt >= 15, "red",
"blue")
Any additional advice would be appreciated.
Hi @JB ,
Try including ALL here:
Proud to be a Super User!
Datediff was the issue, I didn't realise days in the past were presented as negative values. My original code works now, without needing to filter.
Contract RAG lights =
var _dt = datediff(TODAY(), max(Revised[Week Ending]), day)
return
Switch( TRUE(),
_dt >= -7, "light green",
_dt >= -14, "orange",
_dt <= -15, "red",
"blue")
Thanks for your help.
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |