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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.