[DAX] Check if a value from table 1 is present in table 2 (with table 2 filtered by date)

[bBI]

Been wreacking my brain on this one. I manage to check with COUNTROWS and LOOKUPVALUE but as soon as I add date filters it breaks down. Hoping to get some help from the community.

The idea is to add to the sales table a calculated column which shows whether the customer visited in the 5 months before their sale our facebook page.

Below two anonymized tables by way of an example:

SalesTable

Date of Sale	Customer name
2021-03-16	Fred Flintstone
2021-03-18	Wilma Flintstone
2021-03-16	Barney Rubble
2020-12-23	Betty Rubble

VisitorTable

Date of visit	Visitor name
2020-12-28	Fred Flintstone
2020-06-15	Fred Flintstone
2020-07-18	Wilma Flintstone
2020-08-15	Wilma Flintstone
2020-09-12	Wilma Flintstone
2020-12-28	Barney Rubble
2021-03-14	Barney Rubble

Result should be:

Date of Sale	Customer name	Recently visited?
2021-03-16	Fred Flintstone	yes
2021-03-18	Wilma Flintstone	no
2021-03-16	Barney Rubble	yes
2020-12-23	Betty Rubble	no

Fred visited 4 months before purchase ==> yes

Wilma visited 6 months before purchase ==> no

Barney visited 2 days before purchase ==> yes

Betty never visited ==> no

Any pointers as how to achieve such result with dax would be very much appreciated

[V-pazhen-msft · Accepted solution]

@bBI 

You can just create the following measure, check attached pbix if needed.

Measure = IF(MAX([Customer name])in VALUES('Table (2)'[Visitor name]) && MAX([Date of Sale])<=EDATE(MAX('Table (2)'[Date of visit]),5),"Yes","No")

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[OwenAuger]

Hi again @bBI 

We do have to be careful calling CALCULATE in a calculated column, as it causes "context transition" where the current row of the table (and related tables on the 1-side of many-1 relationships) is added to the filter context.

This can interfere with what we are trying to calculate.

Here is a version that should work. It uses REMOVEFILTERS to remove filters from Visitor that have resulted from context transition, for the purpose of this calculation.

Recently visited? = 
VAR CurrentDateOfSale = 'Sales'[Date of Sale]
-- Check if Customer visited between these dates
VAR DateRangeStart = EDATE ( CurrentDateOfSale, -4 )
VAR DateRangeEnd = CurrentDateOfSale
-- Do any rows exist for current customer in the 4 month date range?
VAR RecentlyVisited =
    CALCULATE ( 
        NOT ISEMPTY ( Visitor ),
        REMOVEFILTERS ( Visitor ),
        Visitor[Date of visit] >= DateRangeStart,
        Visitor[Date of visit] <= DateRangeEnd,
        TREATAS ( { 'Sales'[Customer name] }, Visitor[Visitor name] )
    )
RETURN
    IF ( RecentlyVisited, "yes", "no" )

 If it's still not working, could you post a relationships diagram?

All the best,

Owen

[bBI]

This solved the issue

[OwenAuger]

Hi @bBI, 

You could create a column like this (this doesn't assume any relationships between tables or that you have a date table):

Recently visited? = 
VAR CurrentDateOfSale = 'Sales'[Date of Sale]
-- Check if Customer visited between these dates
VAR DateRangeStart = EDATE ( CurrentDateOfSale, -4 )
VAR DateRangeEnd = CurrentDateOfSale
-- Do any rows exist for current customer in the 4 month date range?
VAR RecentlyVisited =
    CALCULATE ( 
        NOT ISEMPTY ( Visitor ),
        Visitor[Date of visit] >= DateRangeStart,
        Visitor[Date of visit] <= DateRangeEnd,
        TREATAS ( { 'Sales'[Customer name] }, Visitor[Visitor name] )
    )
RETURN
    IF ( RecentlyVisited, "yes", "no" )

Regards,

Owen

[bBI]

Hello Owen,

Thanks for the code. I was unaware of the TREATAS() function.

Just pasted it into my report with the sample data above.

Everyone is coming up as "no".

I dialed up the months to -200 and the result remains "no" for all customers.

Do you know what could be the issue?

[bBI]

@OwenAuger ok, very weird but when I remove the relationships each table had with my Date table the DAX works.

However I do need those date relations for other measures and visuals.

Any idea how to solve this? Your input would be much appreciated