If Condition: Date comparison from two different tables

[NewbiePowerBye]

Hello,

I have the following tables(as a preview):

Table A:

ID     InProgressDate    SolvedDate

1       2/5/2018                2/7/2018

2       2/6/2018               2/8/2018

3       4/6/2018                4/9/2018

Table B:

Phase     Start Date            End Date

Phase1       2/01/2018           2/10/2018

Phase2        04/02/2018           05/10/2018

What I want is to make a visual table to see table A Columns but with another column stating in which Phase each ID situates based on InProgress or SolvedDate(If InProgressDate or  SolvedDate are between  Start Date & End Date :

ID     InProgressDate    SolvedDate     Phase

1       2/5/2018                2/7/2018        Phase1

2       2/6/2018               2/8/2018         Phase1

3       4/6/2018                4/9/2018        Phase2

Can someone help me? I can't find a solution.. 😞 

Thank you in advance!

[Greg_Deckler · Accepted solution]

I would think that your column would go something along the lines of:

Column = 
MAXX(FILTER(ALL('Table B'),'Table B'[Start Date]>=[InProgressDate] && 'Table B'[End Date]<=[SolvedDate]),[Phase])

[Greg_Deckler · Accepted solution]

I would think that your column would go something along the lines of:

Column = 
MAXX(FILTER(ALL('Table B'),'Table B'[Start Date]>=[InProgressDate] && 'Table B'[End Date]<=[SolvedDate]),[Phase])

[NewbiePowerBye]

Thanks @Greg_Deckler, it did the job! 😄 

[NewbiePowerBye]

@Greg_Deckler Any idea if I can do this from the query editor and not from DAX? Is there any method?

[Greg_Deckler]

That would be a question for @ImkeF.

[NewbiePowerBye]

@ImkeF can you please help?

[ImkeF · Accepted solution]

You can add column to your Table A like so:

let
    Source = TableA,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.SelectRows(TableB, (tblB) => tblB[StartDate] <= _[InProgressDate] and tblB[EndDate] >= _[SolvedDate])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Phase"}, {"Phase"})
in
    #"Expanded Custom"

code in "Add-column"-dialogue:

[NewbiePowerBye]

Thank you a lot for the help @ImkeF! It worked! 🙂