Sales is the transaction information. Each line in this table has a unique Sale ID.
Payments is the payments information associated with the Sale ID. In a payment cycle we may end up retrying a credit card several times if the payment fails first time. See example below - the same Sale ID but three different Payment IDs associated with that sale.
Assuming I have my data model correct, I really struggle with getting the two fact tables to interact. My reports tend to look at insights only from dimension to fact tables and not between the fact tables.
Are there DAX formulas that work well with interacting between fact tables?
Are there modules on Enterprise DNA that look at best practices on gaining insights between fact tables?
For example, I might want to report on how many credit cards are successfully debited on the first debit attempt, how many are successfully debited on the second attempt etc.
I’m not convinced you need two fact tables here. In similar circumstances, I have had really good success with a combined Transactions table as my sole fact table. This is a really simple, but extremely powerful structure, that leverages the “long and thin” shape I discussed last night:
Transactions
Transaction ID
Transaction Type
Transaction Date
Customer CRN
Sales Offer ID
Transaction Status
Payment Type
In the example you gave, it would be very straightforward to group on Transaction ID and Status, and then count the number of records for each Transaction ID where status equals “Failed”.
Whether this will work for your analysis or not will depend on the questions you have to answer, but it’s quite difficult for me to think of a relevant question where we couldn’t develop the DAX or M relatively straightforwardly to work on that structure.
Give it some thought, and maybe put together some mock data – let me know what you think. Would be glad to continue to brainstorm this out with you.
Great to hear you are finding the Accelerator series helpful.
Yes, what you’re referring to is known as incremental refresh, and since last Fall it is available for Pro users (previously only Premium users). Check out the post below from EDNA Expert @Harsh , which includes extensive resources on setting up and using incremental refresh.
As to the first part of your message, I’m not sure when you say “combine the two data sets on our CRM”, whether you are trying to do that restructuring within the CRM itself or loading the data into PBI and doing the transformations within Power Query? The latter definitely should be possible, although I’m wondering how many records we are talking about?
I used Merge Queries in PQ to combine the Sales and Payments. However, the new table is very slow. I think the combined table has too many fields - not long and thin.
I’m going to try to narrow the Sales and Payments tables prior to combining them.
Yeah, 550K records shouldn’t be causing a performance bottleneck. What about the general structure of the combined fact table though - does that looks like it’s going to work for you if we can resolve the performance issues?..
Sorry, haven’t had a chance to look closely at the combined fact table but it looks okay.
The core measures are working fine.
Any thoughts on how I can tackle the scenario mentioned above? - reporting on how many credit cards are successfully debited on the first debit attempt, how many are successfully debited on the second attempt etc per month?
Yes, Power Query will make this easy. Do an All Rows Group By on Sales ID and Payment Status with your aggregate measure being Count Rows. This will give you for each Sales ID the number of failed payment statuses. Let’s assume we call that column Number of Fails.
Then when you close and apply, create a DAX measure that does a distinct count on Sales ID and then put that in the table with Month & Year and Number of Fails as your other columns.
Here’s a video that goes through the All Rows Group By technique:
If you have any problem, just provide some sample data and we can work through it.
Hi @KieftyKids, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @KieftyKids, we’ve noticed that no response has been received from you since July 26. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @KieftyKids, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
