data model relationships not working

Copper Contributor

Hi I have built relationships between my data tabel in power pivot.  I have tried to avoid many to many relationships by connecting everything to a unique look up value table.  All relationshsips say they are active yet when i try and drag and drop field from different table into a pivot table it suggest that there are NO relationships. 


Upon clicking manage relationships i can see they are all still active 

Digram view issue.png

pivot table error.png

 As you can see im just left with a useless value.  Someone please help me !!


Many thanks

11 Replies

@zedders1250 The message suggest that relationships MAY be needed. It doesn't say that there are no relationships.

You don't show the set-up for the pivot table. Which fields / measures from which tables did you use?

@Riny_van_Eekelen It doesnt matter which fields i use in the pivot table.  It doesnt work unless the fields are from the same table.


Even if i use the most basic relationship so a field from custom contact received and a count of bp from lookup table i get the same message advisng that relationshis may be needed

@zedders1250 Well, if you don't share more info and just repeat that it doesn't work, I can't help you.

I answered your question. What exactly do you need to know?

@zedders1250 Difficult to answer if you don't tell what your are doing, but it seems you are counting BP's from the dimension table based on a column from one of the fact tables. Then you are filtering against the direction of the relationship. It will work, however, when you either put the BP in the Row or Column field.


Since you didn't share any data to work with, I made a mock-up to demonstrate. See attached.


Unfrtunatly i cant share the data due to data protect but ive created an example and attached . I know what you mean about the flow of the relationship but i cant change the direction of the reltion as there duplcate values in the other table. The example i've attached is very simplistic but in my real world use case there are 27k BPs and it isnt possble for me to put then in the rows. I just wanted to show how many unique accounts from the look up table had had some sort of contact or didnt show in contact data 

@Riny_van_Eekelen seems im unable to upload a speadsheet in the same way that you have 

@zedders1250 Perhaps you can share it via Onedrive or GoogleDocs. 

@zedders1250 Attached my mock-up again with a measure called BPcount using CROSSFILTER that allows you to filter from the many-side to the one-side using BP from the dimension table. I believe that's what you need.

yes i think thats. Frustrating as power bi has an option to filter in both direction on their data model. TThanks for you r help

@zedders1250 True, Power BI is more flexible in that respect!