Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
pbi1908
Helper III
Helper III

Interactive Visuals between 2 Fact tables

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_NRTYPEPRODUCT_CATEGORYSUB_CATEGORYC_S_TYPECUSTOMER_CODESUPPLIER_CODE
1SALE12CUSTOMER100NULL
1-001PURCHASE12SUPPLIERNULL200
1002PURCHASE13SUPPLIERNULL300
2SALE24CUSTOMER500100
2-001PURCHASE22SUPPLIERNULL700

 

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.

1 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 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors