Power BI Paginated Report Subreport too many rows fetched

[keobrie]

I have a Power BI Paginated Report that has a subreport.  The Subreport has a single parameter.  The data source for the subreport has about 100,000 records but for a given row on the master report there are typically 1 to sometimes 3-4 records.  When I limit the master report to a single line item via a parameter, the service fetches that one item and then it appears to fetch the 100,000 records for the subreport even though it will only render the handful of records in the subreport where the parameters match.

If I select the master report with 5 items, the service fetches the 5 items and then fetches 500,000 records before rendering the handful of records.

The master report has roughly 20,000 items so when I try to render the full report, it continues to fetch into the 10's of millions of records.
Is there a design approach that will avoid this cartesian product approach other than limiting the report?  This is a master data quality report and it would be helpful not to have to produce the information one master record at a time.  SSRS used to cut through this use case with parameter driven stored procedures with no problem and never generated the cartesian product effect.

[keobrie]

Update 2:

I think I found the culprit but I don't know if I can do anything about it and if I can do something about it how I can do so safely.  I opened and inspected the xml in the rdl.  There is a secondary set of queries tied to the parameters with the following DAX Command:
EVALUATE
SELECTCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL (
                'CCLF2_Output'[cur_clm_uniq_id],
                "h0"
            )
        ),
        "ParameterLevel",
            IF (
                [h0],
                0,
                1
            )
    ),
    "ParameterCaption",
        SWITCH (
            [ParameterLevel],
            1, 'CCLF2_Output'[cur_clm_uniq_id],
            "Blank()"
        ),
    "ParameterValue", 'CCLF2_Output'[cur_clm_uniq_id],
    "ParameterLevel", [ParameterLevel],
    "'CCLF2_Output'[cur_clm_uniq_id]", 'CCLF2_Output'[cur_clm_uniq_id]
)
ORDER BY
    'CCLF2_Output'[cur_clm_uniq_id],
    [ParameterLevel]

I executed this DAX command in the DAX Editor and sure enough it renders the 593,288 rows rendering in 6.5 seconds.  This DAX command is represented in the xml twice in a section that appears to refer to the parameters.  Interestingly in this version of the report, I have removed all parameter references altogether.  So it appears that the report designer may be leaving behind componentry that the Power BI Service is identifying as active.

How can I de-activate this functionality?  If there is no need to populate the parameter values the system should not default to scanning all of the values that are not needed.

[keobrie]

Update:

I have continued to test this with the following findings:

Using DAX Studio I have confirmed that

• the parameter driven DAX query only returns 2 records and executes instantaneously (65 ms)
• the table supporting the subreport has 1,561,123 rows
• the column applicable to the parameter has 593,287 discrete values
• it appears at runtime in the service, the query runs as intended fetching the 2 records, pauses for a half-second or so, then for some reason re-executes a fetch that reads all of the 593,287 discrete values
• then, if the subreport is executed under the master report, the 593,287 record fetch is repeated for each of the records in the master report.  So if there are four claim ids requested, the system attempts to fetch roughly 2.4 million records before rendering the results, even if all four claim ids result in no records for the subreport, or 1 or 2, or 3.
• With 52,283 records in the master report, this behavior does not permit the rendering of the report
• When the 52,283 records are rendered without the subreports the report renders in about 13 seconds.

Diagnostics
Report ID = 9a2b26f0-b11b-45cf-b8da-a7a83c242da9
Report name = rpt_CCLF1_by_Merge
Capacity throttled = No
Request ID = d832c6aa-1537-7f02-96d2-1910c8780452
Performance metrics
Data retrieval time = 1598 ms
Row count = 52283
Processing time = 13621 ms
Rendering time = 52 ms
Render information
Rendering ID = 484a47fc-c609-4c80-967e-c5817a24418e
Start time in UTC = 2024-05-06T19:41:37
Render format = JSONRPL
Content size = 155 KB

[keobrie]

I'm sorry but I have reviewed all of these documents.  This report is using a Power BI Semantic Model as a data source.  The parameter has been properly defined.  All multi-parameter functionality has been disabled.  The parameter does not use a query and must be manually entered.

When the subreport is tested with a manually entered parameter that should yield 2 records, it initially fetches 2 records, pauses for about a half-second, then goes on and fetches 500,000 records, and then renders 2 records.  The diagnostics report 500,000 records fetched.  There is no known basis for the 500,000 record fetch, unless there is something within the following DAX that is triggering it.

DEFINE
VAR vCCLF2Outputcurclmuniqid1 =
IF(
PATHLENGTH(@CCLF2Outputcurclmuniqid) = 1,
IF(
@CCLF2Outputcurclmuniqid <> "",
@CCLF2Outputcurclmuniqid,
BLANK()
),
IF(
PATHITEM(@CCLF2Outputcurclmuniqid, 2) <> "",
PATHITEM(@CCLF2Outputcurclmuniqid, 2),
BLANK()
)
)

TABLE NewTable = FILTER(
'CCLF2_Output',
'CCLF2_Output'[cur_clm_uniq_id] = vCCLF2Outputcurclmuniqid1 && NOT(ISBLANK('CCLF2_Output'[cur_clm_uniq_id]))
)

EVALUATE
SUMMARIZECOLUMNS(
NewTable[cur_clm_uniq_id],
NewTable[bene_mbi_id],
NewTable[clm_line_hcpcs_cd],
NewTable[HCPCS/CPT Code],
NewTable[hcpcs_1_mdfr_cd],
NewTable[In CY2022 Included List?],
NewTable[Short Description],
NewTable
)

[v-yohua-msft]

Hi, @keobrie 

You mentioned that there is only one parameter for subreports. Make sure to use this parameter effectively to filter the data in the source. This means that the query in the subreport should return only the necessary records based on the parameters passed from the main report. You can check the following documents:

Subreports in Power BI paginated reports - Power BI | Microsoft Learn

Add a subreport and parameters to a Power BI paginated report - Power BI | Microsoft Learn

Try to optimize your SQL queries, use stored procedures, or modify how your data is structured, you can refer to the following link:

Optimization guide for Power BI - Power BI | Microsoft Learn

If you encounter some issues during optimization, you can also troubleshoot subreports in Power BI paginated reports:

Troubleshoot subreports in Power BI paginated reports - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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