Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
In report builder I want to add conditional formatting to a column (RFH_DUE_DATE) in a table, the table's main dataset is from the Tracker dataset. I created RFH_DUE_DATE from the Provider dataset.
The column is created from a Lookup expression: RFH_DUE_DATE= IIf(IsNothing(Fields!RPR.Value), DateAdd(DateInterval.Day, 180, Lookup(CInt(Fields!P_ID.Value), CInt(Fields!ID.Value), CStr(Fields!NPR.Value), "Prov")), DateAdd(DateInterval.Day, 180, Fields!RPR.Value))
For the conditional formatting I want to change the fill color-
-Finished: Green
-Due Within 30 Days: Red
-Due Within 90 Days: Yellow
-Finished: If the RFH_FILED is not null or PRRB_INDIVIDUAL_CASE__ is not null or NOTES like “missed”
-Due Within 30 Days: If not Finished and the RFH_DUE_DATE – Today <= 30
-Due Within 90 Days: If not Finished and the RFH_DUE_DATE – Today between 31 and 90
Here is my expression for the background fill:
=switch(isnothing(Fields!RFH_FILED.Value) = FALSE, "Green", isnothing(Fields!PRRB_INDIVIDUAL_CASE__.Value) = FALSE, "Green", instr(Fields!NOTES.Value, "missed") > 0, "Green", (isnothing(Fields!RFH_FILED.Value) = TRUE OrElse isnothing(Fields!PRRB_INDIVIDUAL_CASE__.Value) = TRUE OrElse instr(Fields!NOTES.Value, "missed") = 0) AndAlso datediff(DateInterval.Day,IIf(IsNothing(Fields!RNPR.Value), DateAdd(DateInterval.Day,180,Lookup(CInt(Fields!Provider_ID.Value),CInt(Fields!ID.Value),CStr(Fields!NPR.Value),"Provider")),DateAdd(DateInterval.Day,180,Fields!RNPR.Value)), Today()) <= 30, "Red", (isnothing(Fields!RFH_FILED.Value) = TRUE OrElse isnothing(Fields!PRRB_INDIVIDUAL_CASE__.Value) = TRUE OrElse instr(Fields!NOTES.Value, "missed") = 0) AndAlso datediff(DateInterval.Day, IIf(IsNothing(Fields!RNPR.Value),DateAdd(DateInterval.Day,180,Lookup(CInt(Fields!Provider_ID.Value),CInt(Fields!ID.Value),CStr(Fields!NPR.Value),"Provider")),DateAdd(DateInterval.Day,180,Fields!RNPR.Value)), Today()) > 30, "Yellow", 1=1, "Transparent")
When I run the report the entire column incorrectly turns all green, I cannot get any of the other colors in my expression to work.
How can I correct this expression?
You have 3 different branches in your Switch returning the value "Green" - I would temporarily change 2 of these to something else like "Pink" and "Blue" then you should be able to see which branches of the switch are being applied and it should be easier then to look at your expressions and your data to see what is wrong with the logic.