cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bartekjgs Frequent Visitor
Frequent Visitor

[DAX] Getting particular date from one query to another

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:

 

requeststasks.png

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):

  1. A (request submission),
  2. B,
  3. C,
  4. D.

I need to get Request submission (stage A) date from "Tasks" query into "Requests" query.

 

I tried by creating DAX formulas:

  1. in "Tasks":
    "AStageCreatedDate = IF(Tasks[Task Stage] = "A", Tasks[Created], BLANK( ) )"
    and then tried to lookup it into
  2. "Requests":
    "RequestSubmissionDate = LOOKUPVALUE(Tasks[AStageCreatedDate], Tasks[Request ID], Requests[Id])"
    but as it is giving a multiple values - Blanks and Dates I'm looking for - table and an error saying:
    "A table of multiple values was supplied where a single value was expected."
    I was trying to fit "FIRSTNONBLANK" function here but it didn't work as expected for me.

Do you have any other ideas or solutions?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: [DAX] Getting particular date from one query to another

hi, @bartekjgs 

You could use this simple formula

Column = CALCULATE(MAX(Tasks[AStageCreatedDate]),ISBLANK(Tasks[AStageCreatedDate])=FALSE())

Result:

2.JPG

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Nick_M Established Member
Established Member

Re: [DAX] Getting particular date from one query to another

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?

bartekjgs Frequent Visitor
Frequent Visitor

Re: [DAX] Getting particular date from one query to another

Hey @Nick_M,

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 Smiley LOL
Or maybe there's other way I'm not aware of?

I can create some dummy data for that - I will post shortly.

bartekjgs Frequent Visitor
Frequent Visitor

Re: [DAX] Getting particular date from one query to another

So on example:

I have a "Requests" table:

 

request.png

 

And a "Tasks" one:

 

task.png

 

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.:

  1. For Request = "Test2" I want to get "Submission Date" = 13/04/2019,
  2. For Request = "Test4" -> "Submission Date" = 01/08/2018 etc.
Nick_M Established Member
Established Member

Re: [DAX] Getting particular date from one query to another

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. 

bartekjgs Frequent Visitor
Frequent Visitor

Re: [DAX] Getting particular date from one query to another

Requests:

IdTitleCreated DateModified DateStage

1Test101/01/201816/01/2018B
2Test201/02/201916/02/2019C
3Test301/03/201816/03/2018D
4Test401/05/201816/05/2018D
5Test501/04/201916/04/2019C
6Test601/03/201916/03/2019B

 

 

Tasks:

 

Request IDTitleCreated DateModified DateTask StageId

1Test113/03/201828/03/2018A1
1Test103/04/201818/04/2018B2
2Test213/04/201928/04/2019A3
2Test204/05/201919/05/2019B4
2Test213/04/201928/04/2019C5
3Test301/06/201816/06/2018A6
3Test311/05/201826/05/2018B7
3Test301/06/201816/06/2018C8
3Test311/05/201826/05/2018D9
4Test401/08/201816/08/2018A10
4Test411/07/201826/07/2018B11
4Test401/08/201816/08/2018C12
4Test411/07/201826/07/2018D13
5Test502/07/201917/07/2019A14
5Test511/06/201926/06/2019B15
5Test502/07/201917/07/2019C16
6Test611/05/201926/05/2019A17
6Test601/06/201916/06/2019B18

 

 

That seems like a good news! Awaiting your solution then

Community Support Team
Community Support Team

Re: [DAX] Getting particular date from one query to another

hi, @bartekjgs 

You could use this simple formula

Column = CALCULATE(MAX(Tasks[AStageCreatedDate]),ISBLANK(Tasks[AStageCreatedDate])=FALSE())

Result:

2.JPG

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bartekjgs Frequent Visitor
Frequent Visitor

Re: [DAX] Getting particular date from one query to another

Hello @v-lili6-msft,

that seems to work for me, thank you very much!

Best regards,
Bartek.