Table ambiguity error message in Manage Relationships

[SCTDev]

I have an OLAP cube with eight linked axes in the following manner (column names changed to protect client’s information):

Cube1
ID
Axis1ID
Axis2ID
Axis3ID
...
Axis8ID

Note that Axis1/2/3 are each linked to a calendar dimension (DimCalendar), whereas Axis 4-8 are linked to their respective fact dimensions.

I linked the cube and each axis in the Manage Relationships editor but after I link Axis1 and try to link and activate Axis2, I get the following warning message:

“You can't create a direct active relationship between Cube1 and Axis2 because that would introduce ambiguity between tables DimCalendar and Cube1.  To make this relationship active, deactivate or delete one of the relationships between DimCalendar and Cube1 first.”

I am unsure how to proceed.  Do I need to make a separate DimCalendar for each Axis1/2/3 to prevent this ambiguity?

I changed the cross filter direction setting to Single but that didn't resolve the issue.

Thanks for any help you can provide.

[SCTDev]

It appears this is a problem with Power BI when a table has multiple dates that join back to the same calendar table.  From what I read, you need to override the active relationship, but never found a solution where the override is from Cube->Axis1->DimCalendar.  So that implies (I think) that a single report page can only display one date dimension at a time.

I am approaching it from a different angle:  This time I am aggregating my data based on OperationDate and determining my counts for each date type and storing them in separate columns.  But it reduces the connectivity between my OLAP cube and PBI because I have to create a separate table to store these results.

I'd like to hear other approaches if you have any.

[SCTDev]

For anyone else out there searching for a solution to this puzzle... this is the solution I developed.

My database has data stored in this format, one per inventory item:

ID

Event1Date

Event2Date

Event3Date

I wanted to slice with a date X-axis and show the total number of Event1/2/3 counts per operating date.  Some events have not occurred and contain null.  Some dates have no events on them.

In SQL Server, I created a stored procedure to give me counts by date for each Event type and stored each in a temp table.  Then I merged each count by date, and joined the whole result set to a calendar table that has all operation dates so those with nothing for Event1/2/3 will still be included in the table generated.

I linked the table in PBI, and also linked the calendar table.

I am now able to generate a single line graph, by date, that shows three lines that represent each Event.

I also added three slicers, one to select year, another for month name, and a third by starting date of the week (Sunday - Saturday).

This was the only way I could get all this data to display correctly without having a problem with multiple date columns joined to a single date table, and then having to use DAX to override the active relationship.

I very much want to hear how other people solved this puzzle because I figure there is probably a simpler way that I have not found yet.