cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jeroenvb1
Frequent Visitor

Relationships

Hi all,

 

I am trying to load sales and budget data into PowerBI from Dynamics NAV. For this, i have created three queries, customers, customer ledger entries, article budget entries and one Alldates excel list. Eventually i want to graphically represent for each customers the sales and budget per month.

 

So far, so good ive loaded them into PowerBI. The next thing i try to do is create relationships between these four datasets. It works perfectly fine in powerpivot, as you can see below, but when i try the exact same thing in powerBI, it does not work. I cannot make a 'closing' set of relationships. Can somebody help me with this?

 

Thank you in advance,

Jeroen

 

Relationships.JPG

 

1 ACCEPTED SOLUTION

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.

View solution in original post

11 REPLIES 11
ankitpatira
Community Champion
Community Champion

@Jeroenvb1 for each relation that you create in power bi make sure you set cross filter direction to Single and this should let you create all relations. If not please post what error you get when creating the fourth one.

 

Thank you for your response Ankipatira.

I can create the relationships seperately up to three relationships, but not all four together. It seems like PowerBI does not want to create a closing circle which is strange because powerpivot does not give this error. See a screenshot of the error below:

 

Error creating relationships.JPG

Tjena @Jeroenvb1

Problem is occuring because filter is getting passed to other tables.

Can you do following ?

Create another Datum table, a copy may be(call it Klanten Date), and create realtionship of it with Klanten. Kind of breaking the loop.

Hope it helps you !

Also provide some sample data here so we can mimic your problem.

BR,

/Achin

 

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 entriesart budget entriescust ledger entriescust ledger entriesCustomersCustomers

KHorseman
Community Champion
Community Champion

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank You @KHorseman for such a valuable explanation of scenario. I had calculated that in mind but your way of jotting it here is nice.

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.

View solution in original post

Thank you @eskyline. 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.

 

@KHorseman thanks for mentioning this formula. I'll try it sometime when i have a comparable problem.

KHorseman
Community Champion
Community Champion

@Jeroenvb1I frequently work with only inactive relationships. Especially when I have a table that has multiple date columns (think Created On, Modified On, etc). I create a whole bunch of inactive relationships with my date table, then every measure has a USERELATIONSHIP argument to specify how it should behave. That way I can create as many relationships as I want and they never conflict; I just turn them on when I need them and they are ignored every other time. And since it's always stated explicitly there's never any ambiguity if I have to make changes later. I can just look at the measure instead of checking a measure and a relationship to see what it's really calculating.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman, I'm still confused as to why the relationships @Jeroenvb1 is talknig about works in PowerPivot but not Power BI. Furthermore, Power BI swapped my FROM-TO relationships when I wanted the relationships the other way. I have three tables. Let's say sales measures in one by product which is linked to a master product file that details more info on the products. Then I have a location table by state that also points to the measures table that has those same states. Why can't I show total sales of one product in a specific state? This works perfectly in PowerPivot. I must be missing something. 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors