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

Help with Dax

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

CompanyAEStart DateEnd Date
ABC CompanyFred1/1/20212/28/2021
ABC CompanyGinnie3/1/20219/30/2021
ABC CompanyJim10/1/202112/31/2048

 

nh_table

CompanyMembersReporting Month
ABC Company151/1/2021
ABC Company152/1/2021
ABC Company153/1/2021
ABC Company154/1/2021
ABC Company155/1/2021
ABC Company156/1/2021
ABC Company157/1/2021
ABC Company158/1/2021
ABC Company159/1/2021
ABC Company1510/1/2021
ABC Company1511/1/2021
ABC Company1512/1/2021
ABC Company201/1/2022
ABC Company202/1/2022
ABC Company203/1/2022
ABC Company204/1/2022
ABC Company205/1/2022
ABC Company206/1/2022
ABC Company207/1/2022
ABC Company208/1/2022
ABC Company209/1/2022
ABC Company2010/1/2022

 

Here is what it returns:

CompanyMembersReporting MonthLookupvalue
ABC Company151/1/2021Fred
ABC Company152/1/2021 
ABC Company153/1/2021Ginnie
ABC Company154/1/2021 
ABC Company155/1/2021 
ABC Company156/1/2021 
ABC Company157/1/2021 
ABC Company158/1/2021 
ABC Company159/1/2021 
ABC Company1510/1/2021Jim
ABC Company1511/1/2021 
ABC Company1512/1/2021 
ABC Company201/1/2022 
ABC Company202/1/2022 
ABC Company203/1/2022 
ABC Company204/1/2022 
ABC Company205/1/2022 
ABC Company206/1/2022 
ABC Company207/1/2022 
ABC Company208/1/2022 
ABC Company209/1/2022 
ABC Company2010/1/2022 


What I want it to return:

CompanyMembersReporting MonthLookupvalue
ABC Company151/1/2021Fred
ABC Company152/1/2021Fred
ABC Company153/1/2021Ginnie
ABC Company154/1/2021Ginnie
ABC Company155/1/2021Ginnie
ABC Company156/1/2021Ginnie
ABC Company157/1/2021Ginnie
ABC Company158/1/2021Ginnie
ABC Company159/1/2021Ginnie
ABC Company1510/1/2021Jim
ABC Company1511/1/2021Jim
ABC Company1512/1/2021Jim
ABC Company201/1/2022Jim
ABC Company202/1/2022Jim
ABC Company203/1/2022Jim
ABC Company204/1/2022Jim
ABC Company205/1/2022Jim
ABC Company206/1/2022Jim
ABC Company207/1/2022Jim
ABC Company208/1/2022Jim
ABC Company209/1/2022Jim
ABC Company2010/1/2022Jim


Any suggestions on how I can get it this way?


Thank you for the help!  
Julie

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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]
  )
      

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

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

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@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]
  )
      

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!