Need help with parameter filter in Power BI Report Builder

Hello prabavathym ,

You can try this:

1. Passing % as “ALL” in Parameters : In Power BI Report Builder, you can simulate the “ALL” behavior using default values and conditional logic in SQL:

   • Text Parameters (Product_ID and Batch_ID):

     • Set default value to % (or leave blank and handle it in SQL).

     • Your current SQL already handles this well:

       WHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%')
         AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%')

     • This means if the user enters %, it returns all values.
       
       

2. Cascading Product_Name Dropdown

   • To make Product_Name depend on Product_ID and Batch_ID, you need to:

     • Create Parameters:

       • paraProductID → Text

       • paraBatchID → Text

       • paraProductName → Dropdown (Menu)

     • Set Available Values for paraProductName, use a dataset like:

        

       SELECT DISTINCT Product_NameFROM Sales_TableWHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%')
         AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%')

       This ensures the dropdown only shows relevant names based on the other two inputs.

        

3.  Enable Cascading Behavior:
      -  In Report Builder, go to the Product_Name parameter properties.

      - Under Available Values, link it to the dataset above.

      - Under Dependencies, make sure it refreshes when paraProductID or paraBatchID changes.

    

    

   Please feel free to give me a kudo and accept my answer as the solution if it suits you.
   
   HAve a nice day,
   
   Vivien