Relationships between two tables - date slicer not working

[VictorW]

Hi all,

I am trying to link data from two tables so that the date slicer will work with both. I am new to Power BI and tried a few solutions on the forums, but have not been able to resolve the issue. Here are my tables and some of the relevant columns:

• In Table 1 (Lagging Indicator Export), each row is a summary of stats for a project site broken down by month. The date in each row is the first of the month (e.g. 6/1/2019).
• In Table 2 (Incident Master), each row is an individual incident record. I changed the Date format in Edit Queries to match the date in the first table (e.g. it will show up as 6/1/2019 instead of the actual incident date 6/11/2019).
• The important related columns in each table are Division, Branch, Project, and Date, but are named differently in each table. I created a Project Filter Data table to connect the Project columns.

I created a measure in my report to count the number of rows Table 2 with certain filters and used USERELATIONSHIP to activate the relationship between the DateTable and Table 2. However, the data shows up as Blank in the report. I played around with the relationships and got some numbers to show up, but when I use the date slicer or try to create a table visual with data from both Tables, it does not work properly.  Here is how the relationships are set up:

Any help is appreciated!

Thank you.

[Anonymous]

Well, this is a classic of circular dependencies.

You are correcly using USERELATIONSHIP when calculating a value using an inactive relationship, so your value should be correct (at least in theory). Unless seeing the formula and the model, is hard to guess what coudl be wrong.

The other issue you're facing is that inactive relationship are not activated when using slicers. Slicers works only on active relationships (unless you use metrics that are calculated using the USERELATIONSHIP) so your table 2 on a normal visual will never be sliced by the date table. Wouldn't be better to inactivate one of the two on the right?

[Anonymous]

by the way, your pattern is also a different granularities, so this might help

https://www.daxpatterns.com/handling-different-granularities/

[VictorW]

Thanks for the resources and information. I have been trying an alternative solution, but have hit a road block as well.

Essentially, I want a count of incidents where the "Near Miss" column is 0 from the Incident Master table and put the count into the row with the matching ProjectDescription and Date in the Lagging Indicator Report table.

I tried adding a column to the Lagging Indicator Report table with this function:

TOTALCOUNT = IF('Lagging Indicator Export'[ProjectDescription] = RELATED('Incident Master'[Project Number Description]) && 'Lagging Indicator Export'[Date] = RELATED('Incident Master'[Incident Date]), CALCULATE(COUNTROWS('Incident Master'), 'Incident Master'[Near Miss] = 0), 0)

This returns the error: "The column 'Incident Master[Project Number Description]' either doesn't exist or doesn't have a relationship to any table available in the current context."

The Incident Master table and Lagging Indicator Report table have a many-to-many relationship.