Forum Discussion
How to highlight unmatching data based on date fields from different tables
Hi Alizee
You may first download the attached PBIX to see whether this is close to what you want.
I created two measures in the report. The first measure is to play as a visual-level filter on the left table so the table will only show placements whose date begin dates are earlier than the selected month and date end dates are after the selected month.
Date Flag = IF(SELECTEDVALUE('Placement Table'[Date Begin])<MIN('Date'[Date])&&SELECTEDVALUE('Placement Table'[Date End])>MAX('Date'[Date]),1,0)The second measure is to decide colors for different scenarios. I didn't consider the scenario with ORANGE color as currently the left table show placements whose (start date ~ end date) range can cover the selected month. As a result the placements that meet the third condition will not show.
Color = VAR _itemCodes = VALUES('Invoice Table'[Item Code]) RETURN SWITCH(TRUE(),SELECTEDVALUE('Placement Table'[Placement ID]) IN _itemCodes, "GREEN","RED")If you want to show the third condition placements in the left table, I'll remove the first measure filter on it and modify the color measure. Let me know your expected result.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi Alizee
You may first download the attached PBIX to see whether this is close to what you want.
I created two measures in the report. The first measure is to play as a visual-level filter on the left table so the table will only show placements whose date begin dates are earlier than the selected month and date end dates are after the selected month.
Date Flag = IF(SELECTEDVALUE('Placement Table'[Date Begin])<MIN('Date'[Date])&&SELECTEDVALUE('Placement Table'[Date End])>MAX('Date'[Date]),1,0)The second measure is to decide colors for different scenarios. I didn't consider the scenario with ORANGE color as currently the left table show placements whose (start date ~ end date) range can cover the selected month. As a result the placements that meet the third condition will not show.
Color =
VAR _itemCodes = VALUES('Invoice Table'[Item Code])
RETURN
SWITCH(TRUE(),SELECTEDVALUE('Placement Table'[Placement ID]) IN _itemCodes, "GREEN","RED")
If you want to show the third condition placements in the left table, I'll remove the first measure filter on it and modify the color measure. Let me know your expected result.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hello v-jingzhang ,
Thank you so much for your reply, it really was the solution I was looking for in terms of the first part of the problem!
As for the ORANGE color code, it is a filter I would actually like to add to the right table (the invoice table) to highlight the invoice dates for placements that are not within the right time range.
E.g, if I am selecting February 2022, thanks to your filters, the left table will highlight all placements within the range and color-filter them based on if they have an invoice or not. But I would also like to highlight in the right table in orange the invoice date/placement id that do not match any of the placements shown for february. Do you know how I could do it? Would it be through a conditional formating measure as well?
Thank you again for your help, it really cleared the issue for me/
Kind Regards,
Alizée