Forum Discussion
Churn: Combining subscription dates from two data sources
Needs churn rates over time from two data sources.
I have :
| id | start date | end date |
| 1 | 1 | 2 |
| 2 | 1 |
and
| id | start date | end date |
| 2 | 3 | |
| 3 | 3 | 4 |
How do i combine as to get one table to do my measure on or how do I combine within a measure?
All help is appreciated. :).
mikkelakabel , Append table in Power query and the group by and take Min for start and max for end and try
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
https://docs.microsoft.com/en-us/power-query/group-by
In DAX, Create a common dimension id
and then take create a common measure check min and max for blank and use the other one
example
if(isblank(Min(Table1[Start Date])), Min(Table2[Start Date]),Min(Table1[Start Date]))
Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19
3 Replies
mikkelakabel , Append table in Power query and the group by and take Min for start and max for end and try
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
https://docs.microsoft.com/en-us/power-query/group-by
In DAX, Create a common dimension id
and then take create a common measure check min and max for blank and use the other one
example
if(isblank(Min(Table1[Start Date])), Min(Table2[Start Date]),Min(Table1[Start Date]))
Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19
Hi, mikkelakabel
Try to create a calculated table like this:
Table = var _t=SUMMARIZE(UNION('T1','T2'),[id]) var _id=SELECTCOLUMNS(_t,"_id",[id]) var _tt= ADDCOLUMNS(_id, "start", IF(MAXX(FILTER(ALL('T1'),'T1'[id]=[_id]),[start date])<>BLANK(),MAXX(FILTER(ALL('T1'),'T1'[id]=[_id]),[start date]),MAXX(FILTER(ALL('T2'),'T2'[id]=[_id]),[start date])), "end", IF(MAXX(FILTER(ALL('T1'),'T1'[id]=[_id]),[end date])<>BLANK(),MAXX(FILTER(ALL('T1'),'T1'[id]=[_id]),[end date]),MAXX(FILTER(ALL('T2'),'T2'[id]=[_id]),[end date])) ) return _ttResult:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi, mikkelakabel
May I ask if your problem has been solved? Is the above post helpful to you?
If it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.😀
