Issue with Power BI relationships!

Hi Team,

I hope that I can get some help from here. I have created a sample data model similar to one I am working. I have 3 tables and these have following relationships:

When I create table visual and I try to bring columns from 2 tables, it works fine. But when I try to bring column from 3rd table, it gives an error:

sample PBI file_2.pbix (22.8 KB)

How can I setup this model perfectly, so I can use filters from many side of the relationship without activating birdirectional filtering. Please find attached sample excel file and PBI file.

Kind Regards,
Michael

Hi @michaelreddy2019,

Welcome to the Forum!

I’m struggling to understand why “OrderType” is a separate ‘fact’ table?

To me, ordertype is an attribute of a salesorder and therefore should be part of the SalesOrders fact table eliminating the need for that 2nd table and the “relationship issue” all together…

Hello @michaelreddy2019,

Thank You for posting your query onto the Forum.

Firstly, the data model itself is not designed correctly here. Below are the reasons provided as follows -

1). You’ve created a table OrderType with the metrics - “Online” and “Phone” and have assigned CustomerID’s against them and based on that column you’ve created a relationship with the Customers Table. Now, when we drag the columns such as - “Name” from Customer table, “OrderType” column from the OrderType table and “Sales Amount” from the SalesOrders table see what type of results it actually provides. Below is the screenshot provided for the reference -

Image

If you observe the results carefully, the OrderType column is not able to bifurcate the amounts in proportion with the provided two metrics - “Online” and “Phone”. Same amounts are getting reflected for both the metrics this is because you haven’t provided that proporation/bifurcation in your Fact Table i.e. SalesOrders table. You’ve just listed CustomerID, SalesPersonName and SalesAmount in the table. Below is the screenshot provided for the reference -

SalesOrder Table

So here, the first condition itself is violated. That is, you don’t have any information pertaining to the OrderType column in your SalesOrders table.

2). In continuation with the 1st point, even if you try and merge the Customer and OrderType table and convert them into one single table still you’ll face the problems. Because now, you’ll have multiple records of Customer ID’s rather than having unique ones and then you’ll not be able to create “One-to-Many” relationship between the SalesOrders table and Customer table.

So here second condition is also violated. That is, duplication of records will happen for Customer ID column when you try to merge/combine the Customer and OrderType tables.

3). The reason why you’re not being able to bring in the column (SalesPersonName) from the 3rd table (i.e. Sales Table) is because there’s no direct relationship between SalesPersonName column from the Sales Table with the Other Two Tables. The relationship is created based on CustomerID column so any information that is mapped against that column in the Customer and OrderType tables can be dragged and displayed since they’re tied against the same thread.

For example, what you’re actually trying to do is something like this, bring “Customer Name” from the Customer table and “Sales Person Name” from the Sales Person table. At the back end, this is how the results are getting evaluated. Below is the screenshot provided for the reference -

And therefore you cannot bring in the “SalesPersonName” column from the SalesOrders table into the table visual since that field/metric is altogether different and has no direct relationship with the Customer or Order Type tables.

What you can actually do here is, use the SalesPersonName as a slicer outside the table visual in order to slice and dice the results. Below is the screenshot of the results provided for the reference -

SalesPersonName Used As A Slicer

Eventhough if you want to bring in the SalesPersonName into the table visual then you’ll have to create a “Many-to-Many” relationship between the SalesOrders and Customer table which is absolutely not recommended in this type scenario.

Lastly, the answer to this question -

“How can I setup this model perfectly, so I can use filters from many side of the relationship without activating birdirectional filtering.”

It’s never recommended to use fields as a filters/slicers or as a column in any visual which directly comes from many side of the relationship. And therefore, this is the reason why we create Dimension tables in our model so that we can use the fields from those table which acts as a slicer/filter or even brought into the visualizations. Records from many side of the relationships are used for analysis in the form of “Measures”.

I recommend you to please go through the Data Modelling course which is available onto our education portal so that you can design your data model correctly since there’re several violations in the current data model. Below is the link of the course provided for the reference.

As well as I’m also attaching the working of the PBIX file for the reference purpose.

Hoping you find this useful and helps you in designing your data model. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

sample PBI file_2 - Harsh.pbix (32.9 KB)

1 Like

Hello @michaelreddy2019, good to see that you are having progress with your inquiry :slight_smile:

Did the detailed response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @michaelreddy2019, we’ve noticed that no response has been received from you since August 25. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @michaelreddy2019, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.