PowerBI Data Modelling Report Error

[PBIUser-Sap]

I have a data model where views have One-Many relationships. Issue is that we get error on report dashboard when we try to get data from all these tables together, For example attached below is the report dashboard. Also sometimes we get empty report.

We have 4 views here,

Master

Bank_Accounts

Contacts

Address

Below are one to many relationship between these tables with related columns in bracket

Master(Account) ==> Bank_Accounts(CT_Account_No)

Master(Pty) ==> Contacts(PLRNo)

Master(Party) ==> Address(PTRNo)

Issue is that when we try to fetch data from all some/all of the tables we get report error on dahnboard and on some scenarios we get empty value

When Error:

When Empty:

We even tried to set filter directions to both and try but no help!!

Below are excel worksheet data FYI,

Master:

Bank_Accounts:

Contacts:

Address:

Not sure about what I am doing wrong, Please guide.

[vanessafvg]

I think the issue here is that your middle table ie the master, that table should have the many relationship side in it not the one.  your lookup tables have the many and your 'join table' has the one.

so effectively in order to combine all those table together effectively into one visual,  you need to have your master as the many side and your lookup tables as the one side of the relatioships.

you can see that the joins work just fine as long as you dont select more than 2 tables.  Ie the file you sent me over, if you remove one field from the visual you see it works fine and that is true for all the tables.

this is a modelling issue in order to correct this you will need to model your data correct by having the lookup on the one side (contacts, account etc) and the master on the many side.

[PBIUser-Sap]

We still not able to figure out the solution, Can you please suggest if possible.

[PBIUser-Sap]

Hi @vanessafvg 

Today the data we have in the database is such that it creates one to many from master to other tables. The related columns through which we joined these tables were provided by our function team and they are certain that these relationships are meant correctly.

Is there any way to work around you can suggest to get this fixed without changing the real data?

[vanessafvg]

what is the error you are getting?  can you share the error ? click on see details in the error and share that information, almost  impossible to know without the detail,   in order to get data back you need to have a succsessful join, , have you confirmed that the relationship is working properly.    If you can share your pbix that would be the easiest.  You can use dropbox or similar.

[PBIUser-Sap]

Hi @vanessafvg,

Thanks for checking!!

We are getting as below,

I also shared to you the PBIX on your inbox. Kindly let us know if further details required.