Hey everyone! I have a scenario where I am trying to show a report of incidents that occur in the workplace. The report has a summary section that will show number of incident that occurred in the selected week, etc. It also has a table that will show the details and descriptions of incidents. I have included a PBIX file with a watered-down example. Not all weeks have data to show, so for testing, I suggest selecting 2019 as the year and week 44 as the week number.
Test Case 8.pbix (101.8 KB)
The issue I’m facing is that each incident has two different dates associated with it–the date the incident occurred (Incidents[IncidentDate]), and the the date it was recorded in the system (Incident[ReportDate]). Ideally, these are very close to each other, but that is not always the case. The active date relationship is between Dates[Date] and Incidents[IncidentDate]. This makes all the summary stats accurately calculate numbers for incidents that occurred in the selected week. However, the table that shows descriptions of the incidents needs to show incidents where Incidents[ReportDate] is in the selected week.
I have seen many examples in this forum and others regarding the use of inactive relationships and USERELATIONSHIP(), but all those examples seem to use USERELATIONSHIP() to calculate measures, etc., but I am not trying to summarize or calculate anything. I am just trying to filter the data being displayed in a table.
I have probably done a poor job of explaining my need, so please ask follow-up questions if you have any. Any ideas for how to solve that would be greatly appreciated! Thanks!!!