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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
v-binbinyu-msft
Community Support
Community Support

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
v-binbinyu-msft
Community Support
Community Support

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.