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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BigBadBob
Helper I
Helper I

Multiple Fact Table Issue

Multiple fact Table Issue: I am going round in circles trying to get this to work and knew you guys would find it easy 😉.

I have not included the source data as it is confidential. I attempted to build a non-confidential version and it became quite complex and started to raise numerous other issues. Hopefully the below is clearer and explains the issue better.

 

Goal: Manipulate data to allow payment to be allocated against invoice and bucketed into aging buckets.

Issue: Can not find way to combine Table A and Table B to allow me to achieve goal.

 

Fact Table A

Table A is a complete list of all transactions for both sales Invoices and sales receipts. It includes fields:

  • Date Invoiced (blank for receipts)
  • Invoice Reference
  • Transaction ID
  • Transaction Type
  • Amount for each transaction (invoice or receipt)

 

Transaction ID in table A is unique

 

Fact Table B

Table B is a list of all payments. It includes fields:

  • Date (Blanks for invoices)
  • Transaction ID
  • Reverse Transaction ID (This is the transaction ID which an invoice or payment relates to. Example - Transaction ID 1 is a payment for Transaction ID 42 an invoice)
  • Amount Paid for each transaction

 

Transaction ID in table B is not unique

Reverse Transaction ID in table B is not unique

 

Due to downstream issues, the source data can not be manipulated prior to feeding Power BI.

 

Using Query Editor, I tried combining these into one Fact table to manipulate but the problem I was having was:

  • Table B has a many to one “transaction ID” relationship with table A. It also has a many to one “Reverse transaction ID” relationship with table A. Therefore, if you try to merge / create relationship you will duplicate the amount in Table A.
  • Table B can not be used as “main” fact table as it only includes Invoices paid (includes line/s for invoice -Multiple Invoices can relate to 1 payment )and line/s for payments . Hence, if an invoice has not been paid it will have no record on Table B

 

Please can you suggest any ways you think would efficiently solve this - Thanks All

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @BigBadBob ,

 

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!


Best Regards,
Yingjie Li

v-yingjl
Community Support
Community Support

Hi @BigBadBob ,

 

About the amount for transaction, is it the same as the two tables?

When using merging tables, you can merge these two tables as a new merge based on Transaction ID, remove the repeated columns to try to get the expected result.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@BigBadBob , You can create a common dimension of transaction ID and use. Which table you want to allocate a to B ??

 

Transaction IDs = distinct(union(All(A[Transaction ID]),All(A[Reverse Transaction ID])))

 

You can use this dimension to analyze data

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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