cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Super User IV
Super User IV

Re: Multiple Fact Table Issue

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Microsoft
Microsoft

Re: Multiple Fact Table Issue

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.

Highlighted
Microsoft
Microsoft

Re: Multiple Fact Table Issue

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors
Top Kudoed Authors