Need help with parameter filter in Power BI Report Builder

[prabavathym]

Hi all,

I’m working in Power BI Report Builder connected to an Oracle database. I have three fields:

• Product_ID (text input slicer)

• Batch_ID (text input slicer)

• Product_Name (dropdown slicer)

 Relationships

• One Product can have multiple Batches.

• One Batch can also belong to multiple Products.

• Product Name is tied to Product ID and Batch ID

What I want to achieve

• If user enters Product_ID = 123 and Batch_ID = %, it should return all Batch_IDs for Product 123.
• If user enters Batch_ID = ABC and Product_ID = %, it should return all Product_IDs for that Batch.
• After these two filters, the Product Name dropdown slicer should only display the Product Names relevant to the chosen Product/Batch combination.

Current Oracle SQL
SELECT Product_ID, Batch_ID, Product_Name, Quantity, Status FROM Sales_Table WHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%') AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%');

Parameter details:
Product_ID (text input slicer) → no default value, no available value list.

Batch_ID (text input slicer) → no default value, no available value list.

Product_Name (dropdown slicer) → has both default and available values from this query:

SELECT DISTINCT Product_Name
FROM Sales_Table WHERE (Product_ID LIKE :paraProductID OR :paraProductID = '%') AND (Batch_ID LIKE :paraBatchID OR :paraBatchID = '%');

My issue:
This setup works fine in Oracle SQL Developer, but in Power BI Report Builder I’m not sure how to:

Pass % as “ALL” in parameters.

Cascade the Product_Name dropdown so it correctly depends on the first two text slicers (Product_ID and Batch_ID).

Has anyone implemented this kind of cascading parameter logic in Report Builder with Oracle?Should I handle it fully in the SQL queries, or is there a cleaner approach using Report Builder parameter settings?

Thanks in advance

[vivien57 · Accepted solution]

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