PBI Report Builder - Subreport with Parameters

Hi dkernen2 

You’re on the right track by using subreports for pagination, but performance suffers unless the subreport DAX query is filtered by a parameter. By adding a FILTER() clause using @RequestName in the subreport’s DAX query, you ensure only the required data is retrieved. This mimics a WHERE clause and drastically improves performance — especially if the main dataset is large.

Let me know if you want help constructing a specific version of your DAX query or want to explore alternatives to subreports.

DEFINE
    VAR __RequestNameParam = @RequestName -- This will be injected by SSRS
    VAR __FilteredTable = 
        FILTER(
            'Grant Applications',
            'Grant Applications'[Request Name] = __RequestNameParam
        )

    VAR __DS0Core =
        SUMMARIZECOLUMNS(
            'Grant Applications'[Organization Name],
            'Grant Applications'[Amount Recommended],
            'Grant Applications'[Request State Description],
            'Grant Awards'[Awards],
            'Grant Awards'[Awarded],
            'Grant Applications'[Request Name],
            'Grant Applications'[Amount Requested],
            'Grant Applications'[Organization Budget],
            'Grant Applications'[Duration In Months],
            'Grant Applications'[Grant Cycle],
            'Grant Applications'[Workflow Class],
            'Grant Applications'[City],
            'Grant Applications'[State Code],
            'Grant Applications'[Internal Project Summary],
            'Grant Applications'[Rubric Rationale Potential Impact],
            'Grant Applications'[Overall Project Amount],
            'Grant Applications'[Strategy],
            'Grant Applications'[Cap Bldg Type],
            'Grant Applications'[Grant Start Date],
            'Grant Applications'[Grant End Date],
            "R1_R2_Score__Either_IO_", 'Measures Table'[R1+R2 Score (Either IO)],
            "Grant_Applications", 'Measures Table'[Grant Applications]
        ,
            __FilteredTable
        )
EVALUATE
    __DS0Core

@RequestName is a report parameter, injected dynamically.

__FilteredTable filters 'Grant Applications' to only the relevant row(s).

SUMMARIZECOLUMNS is scoped to this filtered table, so the subreport processes only what it needs, significantly improving performance.

If Using More Than One Filter:

VAR __FilteredTable = 
    FILTER(
        'Grant Applications',
        'Grant Applications'[Request Name] = @RequestName &&
        'Grant Applications'[Organization Name] = @OrgName
    )

Make sure the parameter @RequestName is exactly the same type as the field (Request Name). Sometimes SSRS sends it as text while the field may be nvarchar with trailing spaces — use TRIM() if needed.

Always test the DAX query in DAX Studio or SSMS connected to the semantic model to make sure it's returning results fast with the parameter.