Forum Discussion
Conflict between Filter and Measure
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!
Anonymous , Try like
Days from Last Visit= IF(ISBLANK(max('Visit_Database'[Date of Visit])),0 ,DATEDIFF(Max('Visit_Database'[Date of Visit]),TODAY() ,DAY))
2 Replies
Anonymous , Try like
Days from Last Visit= IF(ISBLANK(max('Visit_Database'[Date of Visit])),0 ,DATEDIFF(Max('Visit_Database'[Date of Visit]),TODAY() ,DAY))
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.