Forum Discussion
Calendar Relationship Error assistance (Ambiguity errors)
Hello,
I'm attempting to link a "Date" column from a field called Breakout, to my Calendar field so that the dates are intertwined and a monthly filter can be applied in the data set.
However, because this Calendar field is also linked to another crucial field labeled fVariableData, it creates an ambiguity error.
I need Breakout and fVariableData to both be linked to Calendar. They both have a "Date" column that are the same format so I do not understand why the relationship doesn't work. Can anyone please help? Thank you.
Hi Anonymous ，
Please correct me if I wrongly understood your question.
There can only be one active relationship between two tables in Power BI Desktop .If you have created an active relationship before ,it can only be an inactive relationship later.
I have two methods, you can refer to either.(Calendar Date as a slicer)
(1)Method one:
Crate another table with column[Breakout] called “Table 2”,and then create a one to many relationship between Table 2 and Calendar Date.
Through this way ,you can filter datas from two tables by monthly slicer.
(2)Method two:
Create an active relationship between 'Table'[fVariableData] and 'Calendar Date'[Date], an inactive relationship between 'Table'[Breakout] and 'Calendar Date'[Date].
Then create a measure by USERELATIONSHIP DAX to activate the inactive relationship . But there is one thing to note: Inactive relationships will only be activated when measure is called in visual.
Measure = CALCULATE(SUM('Table'[ID]),USERELATIONSHIP('Table'[Breakout],'Calendar Date'[Date]))
Original data:
Use active relationship:
Use inactive relationship:
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- HotChilli
Community Champion
It means if the relationship is created there would be multiple paths between Calendar and fVariableData. Powerbi can't allow this.
It's usually a sign that you don't have a star schema or the data model is wrong.
- Ashish_Mathur
Super User
Hi,
Uncheck the active relationship box. You may then have to use the USERELATIONSHIPT() function in your measures(s) to get your desired result.
- Ashish_Mathur
Hi,
Share some data, explain the question and show the expected result.
Hi Anonymous ，
Please correct me if I wrongly understood your question.
There can only be one active relationship between two tables in Power BI Desktop .If you have created an active relationship before ,it can only be an inactive relationship later.
I have two methods, you can refer to either.(Calendar Date as a slicer)
(1)Method one:
Crate another table with column[Breakout] called “Table 2”,and then create a one to many relationship between Table 2 and Calendar Date.
Through this way ,you can filter datas from two tables by monthly slicer.
(2)Method two:
Create an active relationship between 'Table'[fVariableData] and 'Calendar Date'[Date], an inactive relationship between 'Table'[Breakout] and 'Calendar Date'[Date].
Then create a measure by USERELATIONSHIP DAX to activate the inactive relationship . But there is one thing to note: Inactive relationships will only be activated when measure is called in visual.
Measure = CALCULATE(SUM('Table'[ID]),USERELATIONSHIP('Table'[Breakout],'Calendar Date'[Date]))
Original data:
Use active relationship:
Use inactive relationship:
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
