Dynamic Filter Propagation

[petersi]

i have a couple of questions about filters.

The first deals with the Dynamic Filter configration. Is it possible to integrate a dynamic filter into the report filter options. I.E. add a dynmaic filter as a filter on the Visual or Page level Filters.

The second is related the the Visual/Page level filters. Are these filteres applied on the result set of the associated query or are they being applied directly on the associated query (i.e. update the associated query before it is excuted).

If Visual/Page level filters are not applied to the associated query how efficient is the report if the associated query would generate a large data set (say millions of records)

[v-ljerr-msft]

Hi @petersi,

---

The first deals with the Dynamic Filter configration. Is it possible to integrate a dynamic filter into the report filter options. I.E. add a dynmaic filter as a filter on the Visual or Page level Filters.

---

I'm not sure I understand what the dynamic filter means in your scenario. If you mean a filter that users can interact with on the report, then there are two modes for interacting with reports: Reading View and Editing View.  And the filtering capabilities available to you depend on which mode you're in.

• In Editing View, you can add report, page, and visual filters. When you save the report, the filters are saved with it. People looking at the report in Reading View can interact with the filters you added, but not save their changes.

• In Reading View, you can interact with any report, page and visual filters that already exist in the report, but you won't be able to save your filter changes.

---

The second is related the the Visual/Page level filters. Are these filteres applied on the result set of the associated query or are they being applied directly on the associated query (i.e. update the associated query before it is excuted).

---

As far as I know, the filters should be used to creates queries by Power BI first, then Power BI sends the queries to pull the information needed with DirectQuery mode. 

Regards

[petersi]

What I was referring to by dynamic filters relates to the Query Editor. The Paramter defintions part is what I meant by dynamic filters -- i.e. a filter that could be defined and not related to an associated column in a dataset. I would like to know if these Query Editor paramters could be used within the report filter defintion section. I.E. a Query Editor paramter exposed in some way (or associated) to a filter on a Report Page/Visual level filter.

From my review I really don't believe the Page/Level filters are used with the data retrieval operations. They only appear to be used within the conexts of a dataset (i.e. after the data was retrieved from the originating data source). But this I would like to be confimed.

[petersi]

I have not received any repsonse to this question. For clarity - this is what I'm attempting:

I have source data in the following organization (from SQL Server):

Product	Version	Machine	Registrant	Zip	SessionTime	Duration
KV	76	OULR	Mike AZ Test	85260	09:58.0	11:53.0
KV	76	OULR	Mike AZ Test	85260	05:18.0	08:31.0
KV	76	OULR	Mike AZ Test	85260	19:33.0	10:24.0
KV	76	OULR	Mike AZ Test	85260	37:33.0	00:13.0
KV	76	OULR	Mike AZ Test	85260	08:21.0	06:33.0
KV	76	OULR	Mike AZ Test	85260	16:19.0	08:39.0
KV	76	OULR	hyper beam wanna be	85555	09:58.0	11:53.0
KV	76	OULR	hyper beam wanna be	85555	05:18.0	08:31.0
KV	76	OULR	hyper beam wanna be	85555	19:33.0	10:24.0
KV	76	OULR	hyper beam wanna be	85555	37:33.0	00:13.0
KV	76	OULR	hyper beam wanna be	85555	08:21.0	06:33.0
KV	76	OULR	hyper beam wanna be	85555	16:19.0	08:39.0

I need the ability to generate datasets that group the above data across different time parts:

Product	Version	Machine	Registrant	Zipcode	DateRange	Average
KP	16	QLCI	Peter The Great	R2C 0P9	2017/Jun	00:20:51:000
KP	16	QLCI	Peter The Great	R2C 0P9	2017/May	00:01:10:000
KV	76	OULR	hyper beam wanna be	85555	2017/May	00:07:42:000
KV	76	OULR	Mike AZ Test	85260	2017/May	00:07:42:000
KV	76	QLCI	Peter Simard (TEST)	H9W 5B9	2017/Apr	00:08:17:000
KV	76	QLCI	Peter Simard (TEST)	H9W 5B9	2017/May	00:01:28:000

I can prepare this datasets statically. I.E. with specific groupings on the originatiing source data on the  SessionTime field. I.E. a dataset grouped on a

• day basis
• week basis
• month basis
• year basis

What I am trying to achieve is have only one dataset with the raw data and group it dynamically where the user can select what grouping they want to view in, i.e. day,week,month,year. Is there a war to achieve this? To be exact I need this ability for Reports. I see a lot on the DAX and special operations but I am new to Power BI and not clear on DAX and how to use to perfrom what I am attempting.

[v-ljerr-msft]

Hi @petersi,

---

I can prepare this datasets statically. I.E. with specific groupings on the originatiing source data on the  SessionTime field. I.E. a dataset grouped on a

• day basis
• week basis
• month basis
• year basis

What I am trying to achieve is have only one dataset with the raw data and group it dynamically where the user can select what grouping they want to view in, i.e. day,week,month,year.

---

If I understand you correctly, I don't think there is an easy way to do it currently.

When you add a date field to a visual in the Axis field bucket, Power BI automatically adds a time hierarchy that includes Year, Quarter, Month and Day. By doing this, Power BI allows your visuals to have time-based interaction with those viewing your reports, by letting users drill-down through those different time levels.

I would suggest you try visualizing your data with the drill down feature to see if it can give you the expected result.

Regards

[petersi]

I am using the drill down option currently. Just see that there are issues that I'm not sure how to resolve. I am specifically displaying the data in the following groups on a date field:

day

month

week

year

The grouping operations are being done via a SQL query operatrion. So I have a resultant 4 datasets on these specific groupings. When I put these datasets into a single graph it always shows bars for all groups even though it is only showing one group at a time. Also the expand all option is not usable in this scenario as there is no relation between the datasets being used. The resultant char looks like the following:

I only want the chart to show the data for the associated group (i.e. x axis) selected. Also, is there a way to disable the expand all option -- expand down one is fine beause I need to see all datasets.

[petersi]

To clarify my question. I am using Direct Query accessing an Azure SQL Server DB. I don't want to use the data loading version as the reports are going to be viewed within an app that is embedding Power BY - and I don't want to always be going in and updating the datasets. This means I am working explicilty with the SQL Srver DB.