Forum Discussion

GuestUser's avatar
GuestUser
Helper V
6 years ago

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 : Co...
Need Help
az38's avatar
az38
Community Champion
6 years ago

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's avatar
    GuestUser
    Helper V
    6 years ago

    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's avatar
      az38
      Community Champion
      6 years ago

      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