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 : 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?

Need Help

3 Replies

  • 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