Help with Dax

[jgiles]

Hi, 
I'm new to Power BI and Dax.  I'm trying to search one table to bring back a value into another table.  Tables are not releated (could be - but it would be many to many if related).  I tried the lookupvalue function but it's not returning everythign I need.  I'm looking for any advice or ideas on how I can do this.  

Here is my dax code for the calculated column lookupvalue

Lookupvalue =

LOOKUPVALUE(

hs_Table[AE],

hs_Table[Company],

nh_Table[Company],

hs_Table[Start Date],

nh_Table[Reporting Month]

)

hs_table

Company	AE	Start Date	End Date
ABC Company	Fred	1/1/2021	2/28/2021
ABC Company	Ginnie	3/1/2021	9/30/2021
ABC Company	Jim	10/1/2021	12/31/2048

nh_table

Company	Members	Reporting Month
ABC Company	15	1/1/2021
ABC Company	15	2/1/2021
ABC Company	15	3/1/2021
ABC Company	15	4/1/2021
ABC Company	15	5/1/2021
ABC Company	15	6/1/2021
ABC Company	15	7/1/2021
ABC Company	15	8/1/2021
ABC Company	15	9/1/2021
ABC Company	15	10/1/2021
ABC Company	15	11/1/2021
ABC Company	15	12/1/2021
ABC Company	20	1/1/2022
ABC Company	20	2/1/2022
ABC Company	20	3/1/2022
ABC Company	20	4/1/2022
ABC Company	20	5/1/2022
ABC Company	20	6/1/2022
ABC Company	20	7/1/2022
ABC Company	20	8/1/2022
ABC Company	20	9/1/2022
ABC Company	20	10/1/2022

Here is what it returns:

Company	Members	Reporting Month	Lookupvalue
ABC Company	15	1/1/2021	Fred
ABC Company	15	2/1/2021
ABC Company	15	3/1/2021	Ginnie
ABC Company	15	4/1/2021
ABC Company	15	5/1/2021
ABC Company	15	6/1/2021
ABC Company	15	7/1/2021
ABC Company	15	8/1/2021
ABC Company	15	9/1/2021
ABC Company	15	10/1/2021	Jim
ABC Company	15	11/1/2021
ABC Company	15	12/1/2021
ABC Company	20	1/1/2022
ABC Company	20	2/1/2022
ABC Company	20	3/1/2022
ABC Company	20	4/1/2022
ABC Company	20	5/1/2022
ABC Company	20	6/1/2022
ABC Company	20	7/1/2022
ABC Company	20	8/1/2022
ABC Company	20	9/1/2022
ABC Company	20	10/1/2022

What I want it to return:

Company	Members	Reporting Month	Lookupvalue
ABC Company	15	1/1/2021	Fred
ABC Company	15	2/1/2021	Fred
ABC Company	15	3/1/2021	Ginnie
ABC Company	15	4/1/2021	Ginnie
ABC Company	15	5/1/2021	Ginnie
ABC Company	15	6/1/2021	Ginnie
ABC Company	15	7/1/2021	Ginnie
ABC Company	15	8/1/2021	Ginnie
ABC Company	15	9/1/2021	Ginnie
ABC Company	15	10/1/2021	Jim
ABC Company	15	11/1/2021	Jim
ABC Company	15	12/1/2021	Jim
ABC Company	20	1/1/2022	Jim
ABC Company	20	2/1/2022	Jim
ABC Company	20	3/1/2022	Jim
ABC Company	20	4/1/2022	Jim
ABC Company	20	5/1/2022	Jim
ABC Company	20	6/1/2022	Jim
ABC Company	20	7/1/2022	Jim
ABC Company	20	8/1/2022	Jim
ABC Company	20	9/1/2022	Jim
ABC Company	20	10/1/2022	Jim

Any suggestions on how I can get it this way?

Thank you for the help!  
Julie

[Greg_Deckler · Accepted solution]

@jgiles Try this:

Lookupvalue Column = 
VAR __Company = [Company]
VAR __Date = [Reporting Month]
RETURN
  MAXX(
    FILTER(
      'hs_Table',
      'hs_Table'[Company] = __Company && [Start Date] <= __Date && [End Date] >= __Date
    ),
    [AE]
  )
      

[Greg_Deckler · Accepted solution]

@jgiles Try this:

Lookupvalue Column = 
VAR __Company = [Company]
VAR __Date = [Reporting Month]
RETURN
  MAXX(
    FILTER(
      'hs_Table',
      'hs_Table'[Company] = __Company && [Start Date] <= __Date && [End Date] >= __Date
    ),
    [AE]
  )
      

[jgiles · Accepted solution]

Thank you so much Greg!  That worked perfectly.  Appreciate the help!