source data contains date I want to allow users to filter on

[aront]

I use power query to manipulate the source data to a table w/counts on various criteria. It would be great if I could allow the user to filter dates in the underlying data source so they could get counts on date ranges of their choice? Any way to do this?

[aront]

I use 64 Bit. If you have large data sets that's preferable. But Power Bi Desktop contains within it the identical tool found in Excel

[WillT]

 Wanted to chip in here with a couple of comments.

"1. Certain types of queries are just easier to express in M...

WinterCountT = List.Sum(Table.SelectRows(#"Grouped Rows", each [Accident Date] = 12 or [Accident Date] = 1 or [Accident Date] = 2)[Count])"

This is really easy to do with a DAX calculated field:

=SWITCH(MONTH([Accident Date]), 12, "Winter", 1, "Winter", 2, "Winter", "Other")

You could quite quickly generate that column for all the seasons. Then you can use that field anywhere in your report, and have the details to hand if you need them. Power BI will automatically do the aggregations when you just use any of the other fields in a visual.

"2. Power Query queries are reusable - I can copy and paste them into Excel and use them for exporting data (something can't do right now in Power BI) or reuse as is in other reports. If I use a calculated field that wouldn't be portable - that's more old style "excel" thinking than a reusable programming language approach"

I think you're talking about using queries between the Power BI Query Editor and Power Query, right? You can copy and paste the DAX formulas into Excel's Power Pivot as well.

"My question is a bit moot since I just discovered something else: Since all 20-30 queries are based on the same data and need to go into the same report, and since Power BI doesn't currently have a way to filter across different pages in the same report, using the calculated field method, the users would have to apply the filter individually in each of the 20 pages of the report!!!"

We did indeed add this capability to add a report-level filter 🙂 Something you can also do is drop that field onto a page as a Slicer, then the use can filter it on the report directly rather than the filter pane. We don't have a slider bar for it yet, but take a look at the custom visuals that are being built (http://visuals.powerbi.com).

Hope this helps you!

[aront]

As I explain above I prefer not doing calculations in the report because 1) they are not as capable as power query 2) they aren't reusable like power query

BTW in your case using Power Query to get the field to be date is a simple transformation & plays to PQs strength. You should try doing at least the basic clean up and transformation in Power Query

[ashley95e]

aront,

Are you using the 32 bit Power Query or 64 bit ?

[aront]

I did the summation calculations inside Power Query instead of using calculated fields. I thought of your suggestion after I posted so yes, that seems what I was doing wrong. I'll try it and hopefully it will solve my problem.

[aront]

Going back to my data and upon further reflection, this solution won't work for what I am trying to do.

Lets me step back for a moment and talk about the use case. I have a big data table extracted from another application and I need to create about 20-30 report tables. Power Query M has several advantages over DAX.

1. Certain types of queries are just easier to express in M - perhaps because I am more familiar with it? BUt for example, if I need to break up data into "seasons" its really quite easy to build an M query that breaks out the month for each row and then use a function to aggregate that into a season:

#"Grouped Rows" = Table.Group(#"Extracted Month", {"Accident Date"}, {{"Count", each Table.RowCount(_), type number}}),
WinterCountT = List.Sum(Table.SelectRows(#"Grouped Rows", each [Accident Date] = 12 or [Accident Date] = 1 or [Accident Date] = 2)[Count]),

Don't have a clue how to do that in a calculated field

2. Power Query queries are reusable - I can copy and paste them into Excel and use them for exporting data (something can't do right now in Power BI) or reuse as is in other reports. If I use a calculated field that wouldn't be portable - that's more old style "excel" thinking than a reusable programming language approach

My question is a bit moot since I just discovered something else: Since all 20-30 queries are based on the same data and need to go into the same report, and since Power BI doesn't currently have a way to filter across different pages in the same report, using the calculated field method, the users would have to apply the filter individually in each of the 20 pages of the report!!!

So currently, the best solution for my case is  filtering the dates in the source file and all the tables in the created reports will be updated. But even when cross-report filters become available, for the above two reasons I still prefer the Power Query way. I know Power Query allows for parameterized queries, so it would be nice if the parameter could be a report slider. Or does anybody else have a clever solution that would work now?

[andre]

I can’t say I understand your question 100% but maybe this will be helpful…

When people talk about a global filter that spans multiple pages in the report, very often it is a scenario when they want to have a common date range across all those pages. Usually that date range is something like Current Month or Current Quarter or Last 30 Days, or something like that.

In order to implement this, you should add a column to your date dimension in your model and the column should compare the date in each record with the current date. Then you can populate the new column with values (Current Month, Current Month – 1, Current Month – 2, etc. or Last 30 Days, Last 30-60 Days, etc.)

Now you can filter all your pages on the date range that you need and every time your model is refreshed, the date logic is re-run and your dashboards will be automatically updated.

Again, this may not be exactly what you are trying to solve but hopefully it will be helpful anyway.

[aront]

Thanks Andre. Interesting idea, but in my use case the periods might not be fied - I want to be able to give the user the ability to accept any range.

[ashley95e]

If I understand your question, I'm doing that now -

-add a date field to your data

-create a table with the data you would like the users to view by date

-use the date field in a separate slicer above the table

this is very basic, you could also use a separate table with Unique dates that can connect to other tables..... 

[aront]

Hi Ashley

I guess I wasnt clear enough in describing my use case. The Date fleld is already there in the source data, but for the reasons I explained above I am using power query to aggregate the data. So the output of the query does not and in fact cannot contain the date (since its an aggregation on various criteria) but the underlying data does have a date associated with each row. If I try to build a slicer from the source table with the date it doesnt effect the results, since the query and its pipeline doesnt get reloaded.

I could have one page in the report that contains only a data slicer linked to the source data. I dont believe that will work, because it's not reloading the data just changing the set in the visual report. I believe the only solution is to create a source query which points to the original loaded and cleaned data and use that as the source in all my subsequent queries. The I can add a date filter on the date field inside the source query and reload the query to the data model with the new slice of data. That reload will propogate and change everything.

[ashley95e]

Seems to me you will probably have to let BI do the calculations - is that possible?  For me, I loaded as much static data I could possibly need in BI - but I havent had a chance to experiment with the calculation possibilities or limitationss- I'm still having issues just keeping the data clean once in BI.

asHley95e

[ashley95e]

...... One other thing, I'm having issues with BI and date fields, specifically Excel date fields. Bi seems at times not to know the field type. I'm still tring to figure that one out - next step is to import into Access- I tried it once ad got an error importing that table into BI.

asHley95e

[Greg_Deckler]

Import all of the data, create a Calculated Field and then add a filter for date. Then the users can filter to any range they like and get the measures calculated for that range. Perhaps you are doing something on the backend i don't understand.