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.
Hello everyone,
I'm facing an issue while trying to "export" one date from one query into another using DAX.
My structure of data looks like that:
Both tables are connected with "one-to-many" relationship as "Request" is only one and is having multiple "Tasks" assigned to it.
Let's assume that stages are (for both tables):
I need to get Request submission (stage A) date from "Tasks" query into "Requests" query.
I tried by creating DAX formulas:
Do you have any other ideas or solutions?
Solved! Go to Solution.
hi, @Anonymous
You could use this simple formula
Column = CALCULATE(MAX(Tasks[AStageCreatedDate]),ISBLANK(Tasks[AStageCreatedDate])=FALSE())
Result:
Best Regards,
Lin
So on example:
I have a "Requests" table:
And a "Tasks" one:
What I want to achieve is new column (or just the proper value) in "Requests" table, lets call it "Submission Date", which will show the "Created Date" for "Task Stage = "A"" so i.e.:
Can you add it so I can copy and paste?
But regarding your question. If you want a new column ( either a calculated column via DAX, or one created in PQ) they both will be processed at refresh time. The catch is that a cacluated column via dax is will not be able to use the Vertipaq engine for compression, so probably better to use Power Query. But if you are worried what you want to accomplish can be done in DAX and Power Query, so can do both methods and just see which is faster.
Requests:
IdTitleCreated DateModified DateStage
1 | Test1 | 01/01/2018 | 16/01/2018 | B |
2 | Test2 | 01/02/2019 | 16/02/2019 | C |
3 | Test3 | 01/03/2018 | 16/03/2018 | D |
4 | Test4 | 01/05/2018 | 16/05/2018 | D |
5 | Test5 | 01/04/2019 | 16/04/2019 | C |
6 | Test6 | 01/03/2019 | 16/03/2019 | B |
Tasks:
Request IDTitleCreated DateModified DateTask StageId
1 | Test1 | 13/03/2018 | 28/03/2018 | A | 1 |
1 | Test1 | 03/04/2018 | 18/04/2018 | B | 2 |
2 | Test2 | 13/04/2019 | 28/04/2019 | A | 3 |
2 | Test2 | 04/05/2019 | 19/05/2019 | B | 4 |
2 | Test2 | 13/04/2019 | 28/04/2019 | C | 5 |
3 | Test3 | 01/06/2018 | 16/06/2018 | A | 6 |
3 | Test3 | 11/05/2018 | 26/05/2018 | B | 7 |
3 | Test3 | 01/06/2018 | 16/06/2018 | C | 8 |
3 | Test3 | 11/05/2018 | 26/05/2018 | D | 9 |
4 | Test4 | 01/08/2018 | 16/08/2018 | A | 10 |
4 | Test4 | 11/07/2018 | 26/07/2018 | B | 11 |
4 | Test4 | 01/08/2018 | 16/08/2018 | C | 12 |
4 | Test4 | 11/07/2018 | 26/07/2018 | D | 13 |
5 | Test5 | 02/07/2019 | 17/07/2019 | A | 14 |
5 | Test5 | 11/06/2019 | 26/06/2019 | B | 15 |
5 | Test5 | 02/07/2019 | 17/07/2019 | C | 16 |
6 | Test6 | 11/05/2019 | 26/05/2019 | A | 17 |
6 | Test6 | 01/06/2019 | 16/06/2019 | B | 18 |
That seems like a good news! Awaiting your solution then
hi, @Anonymous
You could use this simple formula
Column = CALCULATE(MAX(Tasks[AStageCreatedDate]),ISBLANK(Tasks[AStageCreatedDate])=FALSE())
Result:
Best Regards,
Lin
Hello @v-lili6-msft,
that seems to work for me, thank you very much!
Best regards,
Bartek.
Probably best to use Power Query for this type of thing, in my opinion. Can you add some sample data and what you like the expected output to be?
Hey @Anonymous,
I know how to do it in Power Query but it will require merging doesn't it? I want to avoid that as my query is already heavily-loaded so that's the main case I'm trying to use DAX now
Or maybe there's other way I'm not aware of?
I can create some dummy data for that - I will post shortly.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |