Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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. :).
Solved! Go to Solution.
@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
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.😀
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 _tt
Result:
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.
@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