topic Re: Calculated Column Based on two tables in DAX Commands and Tips

Calculated Column Based on two tables

botaac — Fri, 20 Jan 2023 16:43:20 GMT

I have two tables:

Table 1

Name	Email
John Doe	John.doe@email.com
John Doe	John.doe@email.com
Larry Fitz	Larry.Fitz@email.com
John Elway	John.elway@email.com
John Elway	John.elway@email.com
John Elway	John.elway@email.com

Table 2

Name	Email	Date Submitted
John Elway	John.elway@email.com	1/1/2023
Joe Burrow	Joe.burrow@email.com	5/1/2022
Larry Fitz	Larry.Fitz@email.com	12/1/2022
Brian Urlacher	brian.urlacher@email.com	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:

Name	Email	Eligibility
John Doe	John.doe@email.com	No
John Doe	John.doe@email.com	No
Larry Fitz	Larry.Fitz@email.com	Yes
John Elway	John.elway@email.com	Yes
John Elway	John.elway@email.com	Yes
John Elway	John.elway@email.com	Yes

Thank you in advance!

Re: Calculated Column Based on two tables

FreemanZ — Fri, 20 Jan 2023 22:34:14 GMT

hi botaac

try like:

Eligibility =

VAR _date =

LOOKUPVALUE(

Table2[Date Submitted],

Table2[Name],

Table1[Name]

)

RETURN

IF(

_date>=EDATE(TODAY(),-3),

"Yes", "No"

)

Re: Calculated Column Based on two tables

Anonymous — Mon, 23 Jan 2023 08:12:05 GMT

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.