Forum Discussion
Advocate ITable ambiguity error message in Manage Relationships
Advocate IIt 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.