topic Re: Need to filter data on when a row was active in Developer

Need to filter data on when a row was active

RichardBroadley — Thu, 12 Mar 2020 09:09:54 GMT

There are number of date slicers/filters for Power BI that can filter on a single date column within a dataset being within a user selected date range. However, I need a slicer/filter that works the opposite way round. We have records in our dataset that have ValidFrom and ValidTo date columns and what I need to do is filter the rows based on whether or not a user selecte date is within the date range in the ValidFrom and ValidTo columns.

Non of the custom visuals available online appear to do what I want, and I looked at writing a custom visual of my own to do it, but it seems the filtering/interaction between visuals is only setup for checking if a single column in the dataset is within the user selected date range.

Is it possible to have a visual that filters on whether or not a user selected date is within the date range specified in the data rows, and if so, how do I do it?

Re: Need to filter data on when a row was active

dm-p — Mon, 16 Mar 2020 02:13:15 GMT

Hi @RichardBroadley,

If I understand correctly, you won't have much luck finding/using custom visuals to solve this, as they are bound by the same rules as the data model in terms of filtering, as you have discovered.

If you want to filter based on effective/end date ranges, the simplest implementation will be to create a measure that flags your record if its date range matches the selected date in a regular slicer, and then filter by that in any visuals. Note that this technique requires using 1/0 int values rather than TRUE/FALSE, as for some reason boolean measures can't be used as filter values in visuals, but numeric values can.

I believe this post should give you the concept of what you need, the answer marked as the solution covers the final state, but start reading from this post in the thread.

Hopefully this should be all you need. If not, if you can provide some sample data or workbook we can take a stab at it.

Regards,

Daniel

Re: Need to filter data on when a row was active

Anonymous — Mon, 16 Mar 2020 15:34:25 GMT

I would try adding a measure to find whether the selected date is between your valid to/from dates (something akin to the below)

Active Record on Date = var selDate = SELECTEDVALUE('Calendar'[Date]) var isActive = if(and(selDate>=firstdate('Table'[valid from]),selDate<=firstdate('Table'[valid to])),1,0) return isActive

some screenshots for context:

My Data:

My Fields:

My Slicer:

My Filter:

This will filter down the records when the selected date is between the to/from dates. hope that helps