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