DAX ? How to extract a value from another table based the value of multiple columns in that table?

[3lj]

Hi All,

Thank you in advance for any help anyone can offer, I'm currently going round in circles trying to find the right solution here.

I think I need to use DAX, and i've been playing around with CALCULATE, FILTER, RELATED, SELECT COLUMNS and breaking it down into steps, but I keep getting errors.

SITUATION:

TL:DR; Question: How do I extract the relevant coloured dates into my Project Table?

I have two tables, Tasks and Projects. 
For each task, it has a project, and a date that task is completed.

(For example sake, let's say there are 4 stages in total).

I want to use the dates in the Tasks table, to populate a "start" and "end" fields in my Projects table.

Example: Finding The Start Date - Red Date

So for 'project a' in the Projects table, I want to find all the 'project a' rows in the tasks table (Filter?), then on those, I want to find the all th 'Stage 1' (Filter?), this will leave a single row, then i want to look at the date field in 'Stage complete', and return that value to the 'StartDate' field in the Projects table.

This would then be "filled down" to apply to find the start date for project b, and so forth.

Doing the same for the "end date", I imagine would be the same, but instead of looking at Stage 1, I imagine would be lookeing for Stage 4.

Crossing fingers somebody can point me in the right direction!

[Anonymous · Accepted solution]

Hi @3lj ,

If you want a calculated column, please try.

Start Date = 
VAR _table =
    RELATEDTABLE ( 'Projects' )
VAR _mindate =
    CALCULATE ( MIN ( 'Tasks'[Stage Complete] ), _table )
RETURN
    _mindate

End Date = 
VAR _table =
    RELATEDTABLE ( 'Projects' )
VAR _maxdate =
    CALCULATE ( MAX ( 'Tasks'[Stage Complete] ), _table )
RETURN
    _maxdate

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[amitchandak]

@3lj , Seem like they are min and max date

Start = Min(Table[Stage Complete])

or minx(filter(Table, Table[Stage] = "Stage 1" ), Table[Stage Complete])

For End

end =

Max(Table[Stage Complete])

or Maxx(filter(Table, Table[Stage] = "Stage 4" ), Table[Stage Complete])

[3lj]

Yeah, there might be other stages added later, so I'm going to try the second option, which specifcally says "Stage 1".

Although, forgive me, if I don't see it, how does this take into account / filter based on "project a" or "project b"? (i.e. the current "'row" of the Projects Table?). That's what I'm conceptually struggling with 😞

(as wouldn't it find the "first" start date of all of the projects?, not just the project of the current row?)
Thank you for your help and assistance!