Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi all,
I have one table which contains SALES and PURCHASES about sales. In the same table i have a unique CONTRACT_NR (Vertically) and this is Related between Sales and Purchases
For those sales and Purchases i have several Categories and Sub-Categories. I also have SUPPLIERS which could be CUSTOMERS and vice-versa but i can have a unique code for each one since i have a column that indicates what it is (always look the e.g below).
First critical question is should i seperate this table into 2 Fact tables?
The Requirement is to create visuals which will be interactive between each other. So imagine a table only with sales and a table only for Purchases and when i click to a Sale in the other table i need to see the Related Purchases
and vice-versa. For example if i click to Sale 1 i want to see the Purchases 1-001 and 1-002, and if i click to Purchase 1-001 in the other table i need to see Sale 1.
So the second critical question if i seperate these tables into 2 Fact how i will meet this requirement?
CONTRACT_NR | TYPE | PRODUCT_CATEGORY | SUB_CATEGORY | C_S_TYPE | CUSTOMER_CODE | SUPPLIER_CODE |
1 | SALE | 1 | 2 | CUSTOMER | 100 | NULL |
1-001 | PURCHASE | 1 | 2 | SUPPLIER | NULL | 200 |
1002 | PURCHASE | 1 | 3 | SUPPLIER | NULL | 300 |
2 | SALE | 2 | 4 | CUSTOMER | 500 | 100 |
2-001 | PURCHASE | 2 | 2 | SUPPLIER | NULL | 700 |
I read to connect the Fact tables through some dimension like product and Calendar but in my case this is not working becaues if i can't do the cross filter direction between the DIMENSION table and the 2 Facts "BOTH" since i have trouble with granularity. So the visuals are interactive again.
Thanks.
Solved! Go to Solution.
What i did i create as a bridge dimension table in which i have in the same row the Sales and Purchase and 2 Fact tables FT_SALES and FT_PURCHASE, in the Purchase table i have also the SALES_NR since it's a shortcut of the Purchase_nr. Then i linked the FT_SALES with bridge table with one to many relationship and the Bridge table with FT_PURCHASE with many to many through the SALES_NR. That way i have my tables interactive and when i want to link a different table to both i use the TREATAS function that you suggested me in a different post ! @lbendlin
This depends on the business questions you are trying to answer. Usually a clean snowflake schema with separate fact tables is better, but there are cases where you need to use a different data model to answer the questions.
What i did i create as a bridge dimension table in which i have in the same row the Sales and Purchase and 2 Fact tables FT_SALES and FT_PURCHASE, in the Purchase table i have also the SALES_NR since it's a shortcut of the Purchase_nr. Then i linked the FT_SALES with bridge table with one to many relationship and the Bridge table with FT_PURCHASE with many to many through the SALES_NR. That way i have my tables interactive and when i want to link a different table to both i use the TREATAS function that you suggested me in a different post ! @lbendlin
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
102 | |
98 | |
38 | |
37 |
User | Count |
---|---|
152 | |
125 | |
75 | |
74 | |
63 |