Power BI Report Builder: Filter Table using a Multi-value parameter

Gangula — Mon, 12 Aug 2019 15:35:21 GMT

I have been searching online on how to filter a table in Power BI report builder using a Multi-value parameter. I found this documentation from Microsoft, but this looks like it's based on old Query Designer.

I also found a video in youtube, but even that doesn't help as it doesn't use a parameter to filter.

When I right-click on Dataset and click Query in the current report-builder, I do not see any expression to change equals sign (=) to IN. I don't see a Transact-SQL code, I have an interactive window to drag and drop fields.

Even if I switch off the "Design mode", the expression is a little different. It doesn't have a SELECT function, it directly starts with EVALUATE.

Query Designer

I have a parameter called "Multi_Value" in my report which I'm using to filter a table. I have checked the allow multi-select values option in the parameter properties. But even after doing that, it only shows one value when I run the Report and select multiple values from the drop-down.

Report Builder - Preview

Re: Power BI Report Builder: Filter Table using a Multi-value parameter

dm-p — Tue, 13 Aug 2019 06:25:46 GMT

@Gangula wrote:
Even if I switch off the "Design mode", the expression is a little different. It doesn't have a SELECT function, it directly starts with EVALUATE.
Query Designer

Hi @Gangula,

The designer you're seeing relates to a SQL Server Analysis Services data source. These don't support transact-SQL (T-SQL). The connection type for your data source will need to be set to Microsoft SQL Server to work with the tutorial you've linked, e.g.:

The report builder is pretty much the old builder for SSRS (SQL Server Reporting Services) with a new skin, so I believe that most "old" guides should still work, albeit looking a bit different.

If you're using a SSAS/Power BI dataset and want to work with multi-valued parameters, either of the following may help, as the syntax will be different to T-SQL:

If you're using T-SQL for your dataset (I see you have some output in your repor, so looks like something is working), you'd normally add IN (@Parameter) to your WHERE clause and the tool will dynamically generate the SQL before running against SQL Server proper.

Hopefully this helps you out a bit. If not, you may need to share an example of the query you're running and the parameter setup so that we can help to build it for you if you're not sure.

Good luck!

Daniel

topic Re: Power BI Report Builder: Filter Table using a Multi-value parameter in Developer

Power BI Report Builder: Filter Table using a Multi-value parameter

Re: Power BI Report Builder: Filter Table using a Multi-value parameter