Forum Discussion
Relationships
Try playing around a bit with the Cross Filter Direction on each relationship, setting them to Single, as opposed to Both, in this situation might work. That might allow you to define the relationships without making any of them inactive. Of course once you get them defined be sure to check the interactivity on the visuals you have to ensure they are reacting as necessary for your usage scenario.
Hi Tjena,
What exactly do you mean with "filter is getting passed to other tables"? I tried removing all filters on the queries but it did not help any.
I also duplicated the datum table and created a relationship between cust. ledger entries and klantendate and between article budget entries and datum. This indeed breaks the loop but i the problem remains the same. If you want to see see the sales and budget per month, you still need to have some date link between these two. Do you have any other ideas.
I have some sample data but apparently i cannot upload this into this post. Maybe i can send it to you?
Kind regards
Here i have some screenshots of the queries:
art budget entriescust ledger entriesCustomers
Jeroenvb1 It's not filters on queries achinm45 is referring to. If you have relationships between tables, any filter context you create in a report (by placing a measure next to a column for instance) or a measure gets passed along those relationships whenever anything is calculated.
Let's say you have three tables. TableA, TableB, TableC, each with 3 columns. You have a relationship between TableA[Column1] and TableB[Column1], and another between TableB[Column2] and TableC[Column2]. You cannot now create an active relationship between TableA[Column3] and TableC[Column3] because TableA and TableC are already related via the shared relationships with TableB. That's what this error message is telling you. You can't have two active relationship pathways between any pair of tables, because Power BI would have no way of knowing which pathway to use when evaluating a filter context, and the two pathways could result in very different filtered results.
You can however create an inactive relationship, and then write specific measures to reference it via the USERELATIONSHIP function, but the relationship will be ignored in all other cases that don't reference the relationship in that way.
Try playing around a bit with the Cross Filter Direction on each relationship, setting them to Single, as opposed to Both, in this situation might work. That might allow you to define the relationships without making any of them inactive. Of course once you get them defined be sure to check the interactivity on the visuals you have to ensure they are reacting as necessary for your usage scenario.
Thank you Anonymous. I've been trying some with changing the Cross filter direction to single and this worked for me. PowerBI automatically sets it to Both, opposed to powerpivot who sets it to single. That explains why it did work in powerpivot and the same circular relationship not in powerbi.
Anonymous thanks for mentioning this formula. I'll try it sometime when i have a comparable problem.
Thank You Anonymous for such a valuable explanation of scenario. I had calculated that in mind but your way of jotting it here is nice.
