Forum Discussion
Dynamic Filter on a Date/Time Column?
in Power Query you need to use the old school #duration(0,8,0,0)
DateTime.LocalNow()-duration(0,8,0,0)
What I think you're saying is I could create a separate column that contains the date/time values that I want in the 8 hour window, but the rest of the columns wouldn't be filtered on those date/times.
Let me put it another way in SQL Server terms. I'm trying to execute the following query WITHIN Power BI:
SELECT * FROM table_name WHERE table_name.the_date > DATE_SUB(NOW(), INTERVAL 8 HOUR)
Is that possible or do I know to pre-prepare the data using an ETL tool before I load it into Power BI in order to pull this off? I know in other BI tools like Tableau doing this is possible, but I'm not as familiar with Power BI.
but the rest of the columns wouldn't be filtered filters apply to rows, not columns.
You're right, I should have said "rows" or "data".
Does the rest of my post above make sense as far as what I'm trying to do? Basically load all data into Power BI, then "pre-filter" that data within Power BI so only the rows connected to the timestamps from the last 8 hours appear.
yes, that's a reasonable approach. "Aggregate/Filter your data as soon as possible but not sooner"
Let me put it another way because I realize that sounds confusing. I'll use Tableau as an example as that's what I'm more familiar with.
In Tableau, there's two types of filters -- Data Source filters and Report filters.
Report filters are the ones everyone is familiar with where users can open up a report, apply the filters they want so the report changes dynamically. They can clear all filters if they want all the data stored in the report to appear in the report. Every single BI application like Tableau, Power BI, QlikView, etc. has report filters.
Data source filters allow you to "clean up" data before it gets loaded into a report at all. If you filter out data at the data source level, that data is not going to be accessible at all in the report because it doesn't exist.
I'll give an example. Say you have a report that's fed purely from a single Excel spreadsheet with a ton of data and you want to clear out all data that is older than the past year using a date/time column. Of course, you could edit the Excel document itself and clean it up that way. But using Tableau, you can also connect the raw Excel document to Tableau, then use Tableau's data source filter feature to do the same clean-up within Tableau. The latter is what I'm wondering if Power BI has an equivalent to.