Filtering Based on a Parameter

[michaelb]

Is it possible at all to filter a report based on a parameter that can be changed, or at least set at load time for an embedded report?

We are using embedded reports and need to be able to pass a parameter of some sort to control filtering on certain visual elements. The use case is that when someone loads the embedded report they should only see their own data. However, this needs to be on a per-visual basis, as some visuals should still be able to access data from everybody (such as my sales in one visual vs everyone's average in a second visual). Some visuals will use the filter and some will not.

We've tried adding calculated columns that reference a what-if parameter, however since the columns are only calculated once at load-time they will always be blank and will not update if the parameter gets changed.

We've tried adding additional measures that reference a what-if parameter, however these only work at aggregate level and cannot be used as row filters.

Row-Level Security will not work in our case, as we have multiple datasets with the same schema. Different groups of users will get different datasets, and its not feasible to set this up for every user in every dataset.

One possible solution we have found is that using the JavaScript API for embedded reports it is possible to list all visuals and set filters on them in script. This could be used to add the appropriate filters to the relevant visuals, however it seems the report has to fully load before the list of visuals can be returned. This is not ideal because the user will briefly see the unfiltered data before the filter gets applied.

Ideally we would be able to reference a parameter when setting up filters such as a basic filter for "[column] is equal to X" where X is the parameter and is settable via JavaScript API. Dynamic columns that update when a referenced parameter is changed would also work.

[parry2k]

@michaelb I didn't say RLS is the solution here but it need a lot more planning and understanding, it is not a simple question of a Parameter to filter the dataset.

Anyhow, good luck!

[parry2k]

@michaelb I have worked in very large implementation where they want to compare their data with other institutes but still with RLS they were able to see their own data but other data was kept for comparison. it was a very complex implementation but very successful. All of this was achieved by designing a smart data model and complex DAX measure.

The only challenge I see in your case is that you have multiple datasets whereas the project I worked on they have only one large dataset that server all of their customers.

Having said that, in nutshell, this all can be achieved but need a lot of work, it is not as simple an answer as you expect here. A lot of planning needs to be done to make it work, it is going to mix of the data model and DAX measures.

Just my 2 cents.

[michaelb]

Hi @parry2k ,

I don't believe RLS will work for us as we are using PowerBI Embedded for a multi tenant application.

Each tenant has their own dataset (all the same schema) and their own login which is used by embedded reports available to all of their indivudal users. It would not be feasible for us to set up RLS for each individual user across all datasets.

[MFelix]

Hi @michaelb,

In October it was relaese the Dynamic M Query Parameters that you can associate a query parameter to a slicer in your report maybe that will help to solve your problem:

https://powerbi.microsoft.com/en-us/blog/power-bi-october-2020-feature-summary/#_Toc51665421

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#:~:text=En....

https://blog.crossjoin.co.uk/2020/10/25/why-im-excited-about-dynamic-m-parameters-in-power-bi/

[michaelb]

Hi @MFelix,

Unfortunately this solution will not work for us as we are using an OData feed, not a Direct Query, so the parameter binding feature is not available.

Additionally, we need to be able to set the parameter values via the JavaScript API. The values need to be locked and hidden from the user which currently requires us to iterate over all visuals and update the filters accordingly.