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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
keewei87
Frequent Visitor

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/noCustomerLast visit dateLast Visit Time Period
1Customer A1 Oct 2021Between 7-14 days
2Customer B10 Oct 2021Last 7 Days
3Customer C11 Sep 2021More than a Month
4Customer D Not Visited


Visit_Database

Date of Visit Customer Visited
11 Sep 2021Customer C
1 Oct 2021Customer A
10 Oct 2021Customer 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

CustomerLast visit dateLast Visit Time PeriodDays from Last Visit
Customer A1 Oct 2021Between 7-14 days11
Customer B10 Oct 2021Last 7 Days1
Customer C11 Sep 2021More than a Month30
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! 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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))

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors