Forum Discussion
How to develop a data model with multiple facts and dimensions linking to eachother?
Hey guys,
I have the following tables in my data model however I am struggling to understand how to relate these tables correctly to achieve desired results. I want to be able to:
- Select a country and all results filter for that specific country only
- Select a supplier and all the sales, targets and customers are showing for that supplier
- Select a customer and all the sales, targets and suppliers show for that customer
I would like to use; country, supplier name and customer name as slicers on my report.
I have tried for days to get this to work but keep running into inactive relationships, circular relationships etc... I have read all documentation I can find and still don't have an answer. Do I need a bridging table? What do I need to do to make this work?
Note: There is no real relationship between suppliers and customers
Note also: I will be using RLS at a country level to manage users access to data
Could someone please tell me how to model these relationships?
Hi HarrisonBi
How about creating relationships between table “Counties” and table “Supplier”, table “Counties” and table “Customer” ? Through the field “Country” to filter table “Supplier” and table “Customer” .
Then through table “Supplier” and table “Customer” to filter other sales tables .Refer to the screenshot below .
From the screenshot you can see the table “Customer” and table “Online Sales” ,”Photo Sales” are inactive relationship ,you can create measures to make them become active relationships .
Online filter = CALCULATE(MAX('Online sales'[Sale ID]),USERELATIONSHIP('Online sales'[Customer ID],Customer[Customer ID]))Photo filter = CALCULATE(MAX('Photo Sales'[Sale ID]),USERELATIONSHIP('Photo Sales'[Customer ID],Customer[Customer ID]))But there is one thing to note: Inactive relationships will only be activated when measure is called in visual.
The final result is as shown :
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
HarrisonBi , You also need a date table.
Top 3 table and date only need to join with below four tables 1 - Many join single directional
Hey amitchandak ,
I forgot to add the date table thanks but I do have it in my model. I have tried what you suggested but it does not support all my requirements. For example, when I select country 'Australia', it does not show me all the Australian suppliers or customers. It shows me all suppliers/customers across all countries. This is because no relationship exists between countries and Suppliers or Customers. See below
Hi HarrisonBi
How about creating relationships between table “Counties” and table “Supplier”, table “Counties” and table “Customer” ? Through the field “Country” to filter table “Supplier” and table “Customer” .
Then through table “Supplier” and table “Customer” to filter other sales tables .Refer to the screenshot below .
From the screenshot you can see the table “Customer” and table “Online Sales” ,”Photo Sales” are inactive relationship ,you can create measures to make them become active relationships .
Online filter = CALCULATE(MAX('Online sales'[Sale ID]),USERELATIONSHIP('Online sales'[Customer ID],Customer[Customer ID]))Photo filter = CALCULATE(MAX('Photo Sales'[Sale ID]),USERELATIONSHIP('Photo Sales'[Customer ID],Customer[Customer ID]))But there is one thing to note: Inactive relationships will only be activated when measure is called in visual.
The final result is as shown :
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
