Forum Discussion
Relational tables
Dashboard below is drawing from sources from DM faddere 2018 hentet fra fadderskap, DM faddere 2018 hentet fra kontakt and DM Vervegiro reports. I need to include payment from the table below (DM faddere 2018 hentet fra donasjoner). In this case I need to include payment from the 8th of January 2018 – velkomstpakke is what I need to filter on and I also need to include the 20th of Feb 2018 which occurs the nets_starts dato from which comes from table DM faddere 2018 hentet fra fadderskap. What I am ultimately trying to do is to see the lapse times between payments by campaign (get an avg) or by customer but need to get the flow right first
Is this possible?
Thanking anyone that can help me in advance :)
This is how I have the relationships have been managed.
8 Replies
Hi Anonymous
It is complex to understand your dataset.
I don't know the language, could you give me an simple example in English.
I make a simple excel with these tables named Table1~Table4
Please enter some data to illustrate your dataset.
"the lapse times between payments by campaign (get an avg) or by customer"
I don't see any words about "lapse times"," payments", "campaign".
DM faddere 2018 hentet fra fadderskap table1 DM faddere 2018 hentet fra kontakt table2 DM Vervegiro table3 DM faddere 2018 hentet fra donasjoner table4 link table please create these tables in the following sheets just add the columns which needs to be used in your example don't add too many data, just the data can show your requirement it is better to show your expect result besides the example data Best regards
Maggie
Hi,
Thank you so much for contacting and wanting to help me. I hope the information i have provided below makes it a little easier. I have excluded some informantin for privacy reasons.
All tables include many different customers numbers.
DM vervegiro - This is first table which is at the beginning of the customer journey from a sent out direct mail. In this table, the campaign and campaign activities are the direct mail activity where memberships were generated. Payment date in this table, alerts us to the fact that this person wants to start their membership. Customer number and other sensitive information has been removed for privacy issues.
DM faddere 2018 hentet fra fadderskap – includes other information relevant to the customer’s membership. Including the person’s start date which should in theory be the same as the DM vervegiro - payment date.
DM faddere 2018 hentet fra kontakt table – Our organisation sends out a welcome pack to the customer to start the customer on a recurring payment plan. Then the customer returns the form, which we can see in the form received date. Then the next column needs to be taken from DM faddere 2018 hentet fra fadderskap. Nets_starts date.
DM faddere 2018 hentet fra donasjoner – mean all payments made from a customer’s subscription from the beginning of their existance. However, from the below table, I am after the payment was made since the nets_start date and any subsequent ones after that date. The problem is the campaign and campaign activities are sometimes the same or different to the ones in the DM vervegiro so I would need a (greater than) net:starts date to filter on to get than.
This is how it should be set up. I'm not to concerned about the time lapses but mainly to get this flow.
Thankyou again :)
Hi Anonymous
Thank you for sharing so many details.
For your requirement, i'm not very clear.
Also, it is complex of your data.
I'm stuck on making a test by these.
Based on my knowledge, to "include payment from the 8th of January 2018 to the 20th of Feb 2018",
you could create a measure like
Measure=calculate(sum('table name'[payment]),filter(all('table name'),[date]<=date(2018,2,20)&&[date]>=date(2018,1,8)))Best Regards
Maggie
Hi Anonymous
Based on my understanding, you need to show columns/values from different fact tables together into a single visual or report.
In this scenario, it is better to create proper data models and aggregation function to use columns from other tables.
Here are some suggestions regrading creating correct data models and relationships
>>Multiple Data Tables in Power Pivot
Please check your relationship setting, for some tables, it is need to select "cross filter direction:both".
https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships#cardinality
DAX will calculate the measures/columns against the common dimensions and then let you use the values for the different fact tables in the same analysis
>>https://community.powerbi.com/t5/Desktop/Create-a-Calculated-Column-from-Different-Tables/td-p/60453
Besides, sum, max or any other DAX functions you could consider.
