Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
botaac
Frequent Visitor

Calculated Column Based on two tables

I have two tables:

Table 1

NameEmail
John DoeJohn.doe@email.com
John DoeJohn.doe@email.com
Larry FitzLarry.Fitz@email.com
John ElwayJohn.elway@email.com
John ElwayJohn.elway@email.com
John ElwayJohn.elway@email.com

 

 

Table 2

NameEmailDate Submitted
John ElwayJohn.elway@email.com1/1/2023
Joe BurrowJoe.burrow@email.com5/1/2022
Larry FitzLarry.Fitz@email.com12/1/2022
Brian Urlacherbrian.urlacher@email.com9/1/2022

 

 

I want to create a new column using DAX on Table 1 called "Eligibility" where:

IF the inidivudla has a submission on Table 2 from the past 3 months, it says "Yes", other wise "No".

 

Intended Result would be:

 

NameEmailEligibility
John DoeJohn.doe@email.comNo
John DoeJohn.doe@email.comNo
Larry FitzLarry.Fitz@email.comYes
John ElwayJohn.elway@email.comYes
John ElwayJohn.elway@email.comYes
John ElwayJohn.elway@email.comYes

 

Thank you in advance!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @botaac ,

Please try to create a new column with below dax formula:

Eligibility =
VAR cur_date =
    TODAY ()
VAR _date =
    LOOKUPVALUE ( Table2[Date Submitted], Table2[Name], Table1[Name] )
VAR _val =
    DATEDIFF ( cur_date, _date, MONTH )
RETURN
    IF ( ISBLANK ( _date ), "No", IF ( _val > 3, "No", "Yes" ) )

vbinbinyumsft_0-1674461507205.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @botaac ,

Please try to create a new column with below dax formula:

Eligibility =
VAR cur_date =
    TODAY ()
VAR _date =
    LOOKUPVALUE ( Table2[Date Submitted], Table2[Name], Table1[Name] )
VAR _val =
    DATEDIFF ( cur_date, _date, MONTH )
RETURN
    IF ( ISBLANK ( _date ), "No", IF ( _val > 3, "No", "Yes" ) )

vbinbinyumsft_0-1674461507205.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

FreemanZ
Super User
Super User

hi @botaac 

try like:

Eligibility =
VAR _date =
LOOKUPVALUE(
    Table2[Date Submitted],
    Table2[Name],
    Table1[Name]
)
RETURN
IF(
    _date>=EDATE(TODAY(),-3),
    "Yes", "No"
)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.