Interactive Visuals between 2 Fact tables

[pbi1908]

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.

[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.

[pbi1908 · Accepted 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