Forum Discussion

ManikGarg05's avatar
ManikGarg05
Frequent Visitor
1 year ago
Solved

Paginated Report- Cascading parameters(Handling multiple values)

I am working on a paginated report in Power BI Report Builder with five cascading parameters:

  • Year
  • Quarter
  • Month
  • Week Number
  • Date

The cascading parameters work correctly when I select a single value for each parameter. However, when I select multiple values (e.g., January, February, and March), the child parameters (like Week Number) only show data for one of the selected months (e.g., January) instead of all selected months.

Here is the sample code that I am using for year and date only:

DEFINE
    VAR Param_Year = { @Param_Year }

    VAR __DS0Core = 
        SUMMARIZE(
            FILTER(
                'DateTable',
                'DateTable'[Year] IN Param_Year
            ),
            'DateTable'[Date],
            'DateTable'[Year]
        )

EVALUATE
    __DS0Core

ORDER BY
    'DateTable'[Date]

I want to handle multiple value parameters correctly so that When multiple values are selected for a parent parameter, the child parameters reflect all selected values.

How can I achieve this in Power BI Report Builder?

Need Help
Show and Tell

2 Replies

  • girishthimmaiah's avatar
    girishthimmaiah
    Resolver I
    1 year ago

    Your current approach with { param_Year } may not be effectively handling multiple selected values. Instead, consider using the UNION function to manage multiple selections correctly. Updated DAX Code DAX DEFINE VAR Param_Year = DISTINCT(@Param_Year) -- Ensure unique selected values VAR __DS0Core = SUMMARIZE( FILTER( 'DateTable', 'DateTable'[Year] IN Param_Year -- Ensure all selected years are considered ), 'DateTable'[Date], 'DateTable'[Year] ) EVALUATE __DS0Core ORDER BY 'DateTable'[Date] Explanation of Fixes: Using DISTINCT(@Param_Year): This guarantees that all selected values are treated as a unique list, avoiding any duplication or misinterpretation. Using IN correctly: The filter now accurately includes all selected values rather than defaulting to just one. Ensuring SUMMARIZE captures all relevant values: The FILTER function is now effectively applied to return all pertinent records. Handling Multi-Select for Other Parameters (Quarter, Month, Week, Date) For cascading parameters like Quarter, Month, Week Number, and Date, apply the same logic. Example for Filtering by Selected Months DAX DEFINE VAR Param_Months = DISTINCT(@Param_Month) VAR __DS0Core = SUMMARIZE( FILTER( 'DateTable', 'DateTable'[Month] IN Param_Months ), 'DateTable'[Week Number], 'DateTable'[Month] ) EVALUATE __DS0Core ORDER BY 'DateTable'[Week Number] Steps to Apply in Power BI Report Builder Ensure Parameters Allow Multiple Values: Navigate to the Parameter Properties → General Tab. Check "Allow multiple values". Modify Each Dataset’s Filter to Handle Multi-Select: Each dataset for parameters should utilize IN to encompass all selected values. Use DISTINCT(@Param_Name) in your DAX to prevent duplicates and ensure proper multi-selection.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, ManikGarg05 

    If you want to implement multi-selection when connecting tabular models in Report builder, you need to use the RSCustomDaxFilter function. You can learn how to use it in these two articles:

    Solved: RSCustomDaxFilter Documentation - Microsoft Fabric Community

    Introduction to DAX for paginated reports - Simple Talk (red-gate.com)

     

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.