Forum Discussion
Help with dynamic filtering
Hi! I'm having a problem I can't quite resolve creating a report in PowerBI.
I have a table with a start and end date and need users to be able to filter a report dynamically by date range. The complex part of the use case is that the date range filter has to include all data ros that pass through the slicer's range. i.e. If the user wants to see all rows that happen between 10/01 and 12/01, any case where Start Date < 10/01 and End Date > 10/01 (even if > 12/01) would show up, as well as any case where the Start Date > 10/01 and the Start Date < 12/01.
Basically, if any part of the range in the table falls into the range selected by the user in the slicer, the row should show up. I would only exclude things that ended before the slicer's range or started after the slicer's range.
Thanks for your help!!!
With a lot of help from jdbuchanan71 , I was finally able to solve the problem.
First, I created a Calendar table using start and end dates based on my data.Calendar = CALENDAR(MIN('Table'[StartDate]),MAX('Table'[EndDate]))
Then, I added a measure to the main table that gets the filtered values and applies the count and filtering criteria using the recommendations in this post. A DISTINCTCOUNT was enough but I wanted to be super safe.
In this measure, I'm getting the min and max values selected in the slicer and assigning them to variables. Then counting ids based on the required criteria applying conditions in the filter.Number of Projects = VAR MinDate = MIN ( 'Calendar'[Date] ) VAR MaxDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE( DISTINCTCOUNTNOBLANK(Table[Id]) ,FILTER( Table ,OR( AND( Table[StartDate] <= MinDate ,Table[EndDate] >= MinDate || Table[EndDate] = BLANK() ) ,AND( Table[StartDate] >= MinDate ,Table[StartDate] <= MaxDate ) ) ) )
Once all of this was set, all I had to do was to use the new measure for counts, it even worked like a charm after adding related tables to the model (watch out for relationship directionality).Thanks jdbuchanan71 for all your help, this is now working nicely
12 Replies
With a lot of help from jdbuchanan71 , I was finally able to solve the problem.
First, I created a Calendar table using start and end dates based on my data.Calendar = CALENDAR(MIN('Table'[StartDate]),MAX('Table'[EndDate]))
Then, I added a measure to the main table that gets the filtered values and applies the count and filtering criteria using the recommendations in this post. A DISTINCTCOUNT was enough but I wanted to be super safe.
In this measure, I'm getting the min and max values selected in the slicer and assigning them to variables. Then counting ids based on the required criteria applying conditions in the filter.Number of Projects = VAR MinDate = MIN ( 'Calendar'[Date] ) VAR MaxDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE( DISTINCTCOUNTNOBLANK(Table[Id]) ,FILTER( Table ,OR( AND( Table[StartDate] <= MinDate ,Table[EndDate] >= MinDate || Table[EndDate] = BLANK() ) ,AND( Table[StartDate] >= MinDate ,Table[StartDate] <= MaxDate ) ) ) )
Once all of this was set, all I had to do was to use the new measure for counts, it even worked like a charm after adding related tables to the model (watch out for relationship directionality).Thanks jdbuchanan71 for all your help, this is now working nicely
If you have a calendar table in your model that is not connect to your fact table you can use a measure like this.
Record_Count = VAR MinDate = MIN ( DATES[Date] ) VAR MaxDate = MAX ( DATES[Date] ) RETURN CALCULATE ( COUNTROWS ( YourTable ), YourTable[Start Date] <= MaxDate, YourTable[End Date] >= MinDate )Then set a filter on your visual for [Record_Count] Is Not Blank.
The users would set the date range using the date column from the DATES table.
Thank you so much, this is a very nice, simple way to make it work, I really appreciate your insight!
I tried to use this solution you described, it matches my data model perfectly, and I came to an error that I don't understand the root cause for.
The measure I ended up using is similar to the one you posted but it adds a few conditions:DateFilterCount =VAR MinDate = MIN ( 'Calendar'[Date] )VAR MaxDate = MAX ( 'Calendar'[Date] )RETURNCALCULATE (COUNTROWS (Main),(Main[StartDate] < MinDate && Main[EndDate] >= MinDate) || (Main[StartDate] >= MinDate && Main[StartDate] <= MaxDate))
The weird thing is that I have an unfiltered distinct count of row ids of 313 if I only add locations and 316 if I count statuses and if I mix them in a matrix pivot-table style, the count goes down to 302. The model is very simple, there is only one table with all the data and I am not applying any other filters for the status and location columns. Also, if I leave the filter by measure aside, the count is 316 for both columns. Would you know why this might be happening?Huge thanks again!
You should only need to check the start date vs the max date and the end date vs the min date like this.
DateFilterCount = VAR MinDate = MIN ( 'Calendar'[Date] ) VAR MaxDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE ( COUNTROWS ( Main ), Main[StartDate] <= MaxDate, Main[EndDate] >= MinDate )
It is an interaction of the blank [End Date] field when looking at the view by Status. Try changing the measure to this.
DateFilterCount = VAR MinDate = MIN ( 'Calendar'[Date] ) VAR MaxDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE ( COUNTROWS (Main) ,Main[Start Date] <= MaxDate, ( Main[End Date] >= MinDate || Main[End Date] = BLANK() ) )I think I figured out the problem (very strange one). I added the measure for the count and realized it is coherent across all tables but if I count the ids row it is not. I can't make sense of this behavior but it seems like using the measure instead of the ids for counting solves the problem. I'd appreciate any other insights you might have but I know it's probably not obvious.
Huge thanks again, you have been of great help!
If the date table is joined to your fact table for some other reporting you can modify the measure like this.
Record_Count = VAR MinDate = MIN ( DATES[Date] ) VAR MaxDate = MAX ( DATES[Date] ) RETURN CALCULATE ( COUNTROWS ( YourTable ), YourTable[Start Date] <= MaxDate, YourTable[End Date] >= MinDate, REMOVEFILTERS(Dates) )In this example you can see records getting a count when the start and end date fall outside the selected range:
I have attached my sample file for you to look at.
I don't think you don't neet the [Count of Project] in your tables. The [Date Filter Count] is doing what you want, Giving you the count of projects that fall into your date range. Rather than using it as a filter try just showing that measure. I would rename it to [Active Project Count].
