Add Multiple Values in Filter Conditions during report creation

[GuestUser]

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?

[az38]

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

LinkedIn

[GuestUser]

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 

[az38]

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