topic Add Multiple Values in Filter Conditions during report creation in Report Server

Add Multiple Values in Filter Conditions during report creation

GuestUser — Fri, 03 Jan 2020 09:36:00 GMT

Hi,

We have connected Power BI to SSAS - Connect Live Option

We are trying to create one report where a specific filter condition needs to be added (too many values in filters)

eg : Column1 , Column 2 , Column 3 , Column 4 --- in Report View

where Column4 = Value 1 , Value 2 , Value 3 , Value 7 , Value 9.....Value 98 --- Filter Condition

In filtering , i have the option to click on check boxes for different values..(Value1 , value2 etc) but it becomes very tedious when we have more values (say 90 values)

Is there any better option to do?

Re: Add Multiple Values in Filter Conditions during report creation

az38 — Sat, 04 Jan 2020 08:02:15 GMT

Hi GuestUser

I see 2 options:

1. you could use M language (Power Query Editor mode -> pick ypur datasource -> Advanced Editor then find a string like

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column4] = "Value1" or [Column4] = "Value2" or [Column4] = "Value3"))

change it to your needs and press ok.

2. second option, as I see. you can import (or create via Enter Data) datasource like "Allowed values" which contains from the only columns with only values you need in report

Then go to the Table1 (origin data source), press Merge Queries, choose your columns and Inner as join type

you will get a table like this:

remove column "Allowed values" and enjoy!

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Add Multiple Values in Filter Conditions during report creation

GuestUser — Mon, 06 Jan 2020 06:52:56 GMT

Thanks az38 for replying !!

These will work out if i am connected to DB through Import Mode but I am using Connect Live (SSAS) option.

Any suggestions for the same

Re: Add Multiple Values in Filter Conditions during report creation

az38 — Mon, 06 Jan 2020 07:04:07 GMT

Hi GuestUser

the technique would be similar:

1. create a DAX table

AllowedValues = DATATABLE("Allowed Value"; STRING; { {"Value1"}; {"Value3"}; {"Value5"} } )

2. Create relationships your Query1[Column4] - AllowedValues[AllowedValue]

3. Create a table

Filtered Table = NATURALINNERJOIN(Query1;AllowedValues)

do not hesitate to give a kudo to useful posts and mark solutions as solution