Conflict between Filter and Measure

[keewei87]

Dear Powerbi Gods,

My measure and new column/Filter is conflicting with each other.

I have 2 databases. Customer_database and Visit_database.

They are linked by Customer and Customer Visited respectively on 1 to many relationship.

Customer_Database

S/no	Customer	Last visit date	Last Visit Time Period
1	Customer A	1 Oct 2021	Between 7-14 days
2	Customer B	10 Oct 2021	Last 7 Days
3	Customer C	11 Sep 2021	More than a Month
4	Customer D		Not Visited

Visit_Database

Date of Visit	Customer Visited
11 Sep 2021	Customer C
1 Oct 2021	Customer A
10 Oct 2021	Customer B

Last Visit Date and Last Visit Time Period is New Column.

Last Time Period is also used as a filter.

Last Visit Time Period =
IF(ISBLANK('Customer_Database'[Last Visit Date]), "Not Visit",
IF('Customer_Database'[Last Visit Date] >= TODAY()-6, "Last 7 Days",
IF('Customer_Database'[Last Visit Date] >= TODAY()- 13,"Between 7-14 days",
If('Customer_Database'[Last Visit Date] >= TODAY()- 30, "14 days to a Month",
"More than a Month"
))))

Days from Last Visit is a Measure.

Days from Last Visit= IF(ISBLANK(DATEDIFF(LASTDATE('Visit_Database'[Date of Visit]),TODAY() ,DAY)), "0", (DATEDIFF(LASTDATE('Visit_Database'[Date of Visit]),TODAY() ,DAY)))

Dashboard should show

Customer	Last visit date	Last Visit Time Period	Days from Last Visit
Customer A	1 Oct 2021	Between 7-14 days	11
Customer B	10 Oct 2021	Last 7 Days	1
Customer C	11 Sep 2021	More than a Month	30
Customer D		Not Visited

"Last Visit Time Period" Filter is working fine without "Days for Last Visit". It is able to filter accordingly.

Once I added Days from Days from Last Visit, the filter is not working correctly and the data doesn't show correct data too.

I realised through trial and error on Days from Last Visit will show 0 for those visited within last 24 hrs. Not sure if it is causing the conflict.

Grateful if you could help me. Really appreciate it. Thanks in advance! 

[amitchandak · Accepted solution]

@keewei87 , Try like

Days from Last Visit= IF(ISBLANK(max('Visit_Database'[Date of Visit])),0 ,DATEDIFF(Max('Visit_Database'[Date of Visit]),TODAY() ,DAY))

[keewei87]

Thanks @amitchandak. Really appreciate your reply!!

I managed to solve the issue!!

I made the measure a calculated column instead and use the dax format as you provided but max date not working for me. But lastdate works for me.