Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Transaction ID in table A is unique
Fact Table B
Table B is a list of all payments. It includes fields:
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:
Please can you suggest any ways you think would efficiently solve this - Thanks All
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
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.
@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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |