Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm stuck on this one,
I've 2 tables
DF1
ID |
11 |
22 |
33 |
44 |
55 |
DF2
ID | Task | Date 1 | Date 2 |
11 | Act 1 | 12/02/2022 | |
11 | Act 2 | 15/05/2022 | 25/05/2022 |
22 | Act 1 | 05/06/2022 | 01/08/2022 |
22 | Act 2 | 04/02/2022 | 13/02/2022 |
33 | Act 1 | 1/11/2022 | |
33 | Act 2 | 19/12/2023 | 24/05/2022 |
44 | Act 2 | 18/03/2026 | 02/02/2022 |
44 | Act 2 | 12/08/2023 | 17/05/2022 |
55 | Act 1 | 22/05/2024 | 09/09/2020 |
I need to add a column to DF1, the logic for this column is as follows:
Scenario 1 - Where the ID has the Task "ACT 1" use Date 1, If Date 1 is empty Use Date 2.
Scenario 2 - Where the ID has no Task "ACT 1" use Date 2.
Apologies for the table formatting
Thanks,
Solved! Go to Solution.
Hi @obriaincian ,
Approve with @ryan_mayu .
Since you didn't give the logic for calculating Date2 such as ID 44, I'll tentatively return it as blank(If this is not your logic, please provide more details). Dax is as follows:
Column =
var _a = SELECTCOLUMNS(FILTER('DF2',[ID]=EARLIER(DF1[ID])),"Task",[Task])
var _b = MAXX(FILTER('DF2',[ID]=EARLIER(DF1[ID])&&[Task]="Act 1"),[Date 1])
var _c = MAXX(FILTER('DF2',[ID]=EARLIER(DF1[ID])&&[Task]="Act 1"),[ Date 2])
return IF("Act 1" in _a &&_b<> BLANK(),_b,_c)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @obriaincian ,
Approve with @ryan_mayu .
Since you didn't give the logic for calculating Date2 such as ID 44, I'll tentatively return it as blank(If this is not your logic, please provide more details). Dax is as follows:
Column =
var _a = SELECTCOLUMNS(FILTER('DF2',[ID]=EARLIER(DF1[ID])),"Task",[Task])
var _b = MAXX(FILTER('DF2',[ID]=EARLIER(DF1[ID])&&[Task]="Act 1"),[Date 1])
var _c = MAXX(FILTER('DF2',[ID]=EARLIER(DF1[ID])&&[Task]="Act 1"),[ Date 2])
return IF("Act 1" in _a &&_b<> BLANK(),_b,_c)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Let's see ID 44, don't have ACT 1 and has two date 2, then what date will you use?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |