Filtering and Displaying Data Based on Inactive Relationship

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!!!

Hi @DaveC,

So I did change the relationships to inactive, updated the Total measure

Total Incidents = 
    USERELATIONSHIP( Dates[Date], Incidents[IncidentDate] )

and created a visual filter measure

ReportDate Filter = 
    COUNTROWS( Incidents ),
    USERELATIONSHIP( Dates[Date], Incidents[ReportDate] )

With this result.

I hope this is helpful. Here’s my sample file.
eDNA - Test Case 8.pbix (101.5 KB)


If I understand correctly, you need the “Events in progress” pattern, the filtering is done by DAX.
Sam did a video on this

Depending on your requirements you can either use 2 inactive relationships with your date table, or create a separate “Incidents calendar” with 2 inactive relation ships, so you do not have to use USERELATIONSHIP for all your other measures.

Thanks, @Melissa and @Paul! Your replies have made me rethink what I’m doing. In my actual report, the case I presented here is one page in a multi-page report. Everything else in the report uses Incidents[IncidentDate] exclusively, and I would have to go back and rewrite dozens of existing measures if that relationship were to be made inactive.

After further reviewing the samples I have of past reports which were manually compiled into Excel and emailed to recipients, I believe that all the date relationships in the case I described above may be able to use Incidents[ReportDate], so I’m now leaning toward separating it out as it’s own PowerBI report, and making a single relationship between Dates[Date] and Incidents[ReportDate] instead of having to manage two date relationships within the larger report.

Thanks again for your replies. They were very helpful!

Great, as you can tell there are several ways to analyse this kind of data, the power is in the fact that it opens your mind and helps you thinking in the directiuon required for your purposes.
Good luck with it.

Hi @DaveC, Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Group. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!