Forum Discussion

botaac's avatar
botaac
Frequent Visitor
3 years ago
Solved

Calculated Column Based on two tables

I have two tables:

Table 1

NameEmail
John Doe[email protected]
John Doe[email protected]
Larry Fitz[email protected]
John Elway[email protected]
John Elway[email protected]
John Elway[email protected]

 

 

Table 2

NameEmailDate Submitted
John Elway[email protected]1/1/2023
Joe Burrow[email protected]5/1/2022
Larry Fitz[email protected]12/1/2022
Brian Urlacher[email protected]9/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 Doe[email protected]No
John Doe[email protected]No
Larry Fitz[email protected]Yes
John Elway[email protected]Yes
John Elway[email protected]Yes
John Elway[email protected]Yes

 

Thank you in advance!

calculated column
DAX
new column
power bi
tables
  • Anonymous's avatar
    Anonymous
    3 years ago

    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" ) )

    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.

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi botaac 

    try like:

    Eligibility =
    VAR _date =
    LOOKUPVALUE(
        Table2[Date Submitted],
        Table2[Name],
        Table1[Name]
    )
    RETURN
    IF(
        _date>=EDATE(TODAY(),-3),
        "Yes", "No"
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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" ) )

    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.