Forum Discussion
Date slicer with multiple dates and tables
Hello,
I am trying to filter data on dates based on a [createdon] field in multiple tables. I have seen several methods but I have used what I thought was the the simplest.
I have a slicer based on a calendar table 'calendar'[Date] that works fine (see picture).
I have Min&Max variables that are picking up the date range on the filtered calendar table that also work fine (see picture).
I then try to use these Min&Max variables to change the value of a calculated column 'surveyinvite'[DateFilter] using the following formula to add a 1 when the msfp_surveyinvite'[createdon] is within the range and 0 when it is not. This will allow me to add a page filter with 'table'[createdon]= 1 for all the tables:
As you can see from the resulting surveyinvite table, my formula is not working and I am getting 1 in all the rows irrelevant of date.
Any help? I feel I am very close to a solution.
Hi sebco
Are calendar and msfp_surveyinvite two separate tables with no relationship created ? I restored your scene, everything looks fine .
(1)Create a Calendar date table ,and add a slicer with calendar date . Then create two measures to return the Max and Min date when change date in Slicer .
Max Date = MAX('Calendar Date'[Date])Min Date = MIN('Calendar Date'[Date])(2)Then create a measure for data table to judge whether the date in data table is contained in slicer date range , if yes ,return 1 , no return 0 .
Measure = IF(SELECTEDVALUE(Data[Date])>=[Min Date] && SELECTEDVALUE(Data[Date])<=[Max Date],1,0)The final result is as shown :
I have attached my pbix file , you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
parry2k The dataset is from Microsoft Customer Voice with several different relationships. The Calendar table (marked as a date table) will only let me create one relationship with the other tables I need to filter (surveyinvite, customerresponse, questionresponse). Other relationships from the calendar table create primary/secondary issues which will not let me activate them without removing other relationships which I can't do.
- parry2k
Super User
sebco lets you have 3 columns in your transaction table, create date, due date, ship date and you have the relationship between these 3 dates with the date table, and the create date relationship is active, and other two are inactive.
And now we want to have a count of orders by create date, due, and ship date so that we can see these 3 on the same x-axis, maybe month, year, or date. These are the measure we will create and then visualize, pick columns from date dimension and use these measures for values.
Create Date Order Count = COUNTROWS ( SalesTable ) --because this is active relationship Due Date Order Count = CALCULATE ( [Create Date Order Count], USERELATIONSHIP ( SalesTable[DueDate], DateTable[Date] ) ) Ship Date Order Count = CALCULATE ( [Create Date Order Count], USERELATIONSHIP ( SalesTable[ShipDate], DateTable[Date] ) )✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Hi sebco
Are calendar and msfp_surveyinvite two separate tables with no relationship created ? I restored your scene, everything looks fine .
(1)Create a Calendar date table ,and add a slicer with calendar date . Then create two measures to return the Max and Min date when change date in Slicer .
Max Date = MAX('Calendar Date'[Date])Min Date = MIN('Calendar Date'[Date])(2)Then create a measure for data table to judge whether the date in data table is contained in slicer date range , if yes ,return 1 , no return 0 .
Measure = IF(SELECTEDVALUE(Data[Date])>=[Min Date] && SELECTEDVALUE(Data[Date])<=[Max Date],1,0)The final result is as shown :
I have attached my pbix file , you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jimbob2285Advocate IV
Hi Anonymous
Sorry, i know this post is a year old, but I've had the same challenge and come to the same solution as you proposed, which works fine at a granular level, but I'm also summarising by person and it doesn't work at a summarised level... it's just showing a zero
I've added person to the data table and added another visual to the page to demonstrate the problem - I've added a screenshot below, but I can't see how to attach the actual PBIX file
Is there a way to resolve this, I'm hitting my head against a brick wall trying to work this out
Thanks
Jim
