Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GuestUser
Helper V
Helper V

Add Multiple Values in Filter Conditions during report creation

 

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?

3 REPLIES 3
az38
Community Champion
Community Champion

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

12592.png

 

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

12592.png

 

you will get a table like this:

12592.png

remove column "Allowed values" and enjoy!

 

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

LinkedIn

 


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

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
Community Champion
Community Champion

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


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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.