Report Builder - Report will run in desktop but stuck loading rows when run from service

[GeorgeColl]

This question is about a paginated report in Report Builder.

 

I have a paginated report that I can run in my desktop without issue, it takes maybe 20-30 seconds maximum to run but it works as expected.

 

When I publish the report I'm able to input my parameters but as soon as I press run I'm stuck in a loading screen. Is this going to be because of something I'm doing wrong or could it be some other issue?

 

The primary dataset query in this report is below, incase I'm doing something daft there...

DEFINE

    VAR __DS0FilterTable1 =
        FILTER (
            VALUES ( 'XX'[Contract] ),
            PATHCONTAINS ( @Insurer, 'XX'[Contract] )
        )
        
    VAR __DS0FilterTable2 =
        FILTER (
            VALUES ( 'XX'[Insured Name Simplification] ),
            PATHCONTAINS ( @insuredName, 'XX'[Insured Name Simplification] )
        )

    VAR _Collapps = CALCULATETABLE(
	VALUES ( 'AllPolicies'[Policy Period] ),
	PATHCONTAINS ( @InsuredName, 'AllPolicies'[Insured] )
)
    VAR _CA = CALCULATETABLE(
	VALUES ( 'XX'[Policy Period] ),
	PATHCONTAINS ( @insuredName, 'XX'[Insured Name Simplification] )
)

    VAR __DS0FilterTable3 =
        EXCEPT (
	_Collapps,
	_CA
)

    VAR __DS0FilterTable4 =
        FILTER (
            VALUES ( 'AllPolicies'[Insured] ),
            PATHCONTAINS ( @InsuredName, 'AllPolicies'[Insured] )
        )

	VAR __DS0Core = 
		ADDCOLUMNS(SUMMARIZECOLUMNS(
			'XX'[Insured Name Simplification],
			'XX'[Policy Period],
			'XX'[Inception Date],
			'XX'[Claimant],
			'XX'[Open Or Closed],
			'XX'[Claim Code],
			'XX'[Date Notified],
			'XX'[Contract],
			'XX'[Reference],
			'Outstanding Fee and Liability Split - ClaimAmounts'[OS_Fees_CoverageCounsel],
			__DS0FilterTable1,
			__DS0FilterTable2,
			"SumOSFees", CALCULATE(SUM('XX'[OSFees])),
			"SumOSLiab", CALCULATE(SUM('XX'[OSLiab])),
			"SumOSOurFees", CALCULATE(SUM('XX'[OSOurFees])),
			"SumPDFees", CALCULATE(SUM('XX'[PDFees])),
			"SumPDLiab", CALCULATE(SUM('XX'[PDLiab])),
			"SumPDOurFees", CALCULATE(SUM('XX'[PDOurFees]))
			),
			"PD_Fees_CoverageCounsel", CALCULATE(SUM('CAClaims'[PD_Fees_CoverageCounsel])),
			"Incurred", CALCULATE(SUM('CAClaims'[Incurred])),
			"Insured", BLANK()
			)

	VAR __DS1Core =
		ADDCOLUMNS(SUMMARIZECOLUMNS(
			'AllPolicies'[Insured],
			'AllPolicies'[Policy Period],
			'AllPolicies'[InceptionDate], 
			__DS0FilterTable3, 
			__DS0FilterTable4
			),
			"Claimant", BLANK(),
			"Open Or Closed", BLANK(),
			"Claim Code", BLANK(),
			"Date Notified", BLANK(),
			"Contract", BLANK(),
			"Reference", BLANK(),
			"OS_Fees_CoverageCounsel", BLANK(),
			"SumOSFees", BLANK(),
			"SumOSLiab", BLANK(),
			"SumOSOurFees",  BLANK(),
			"SumPDFees", BLANK(),
			"SumPDLiab",  BLANK(),
			"SumPDOurFees",  BLANK(),
			"PD_Fees_CoverageCounsel",  BLANK(),
			"Incurred",  BLANK(), 
			"Insured Name Simplification", BLANK()
			)

	VAR __DS2Core =
		UNION(__DS0Core, __DS1Core)

EVALUATE

    SELECTCOLUMNS(
        __DS2Core,
        "Inception Date", [Inception Date],
        "Insured", [Insured], 
        "Policy Period", [Policy Period],
			"Claimant", [Claimant],
			"Open Or Closed", [Open Or Closed],
			"Claim Code", [Claim Code],
			"Date Notified", [Date Notified],
			"Contract", [Contract],
			"Reference", [Reference],
			"OS_Fees_CoverageCounsel", [OS_Fees_CoverageCounsel],
			"SumOSFees", [SumOSFees],
			"SumOSLiab", [SumOSLiab],
			"SumOSOurFees",  [SumOSOurFees],
			"SumPDFees", [SumPDFees],
			"SumPDLiab",  [SumPDLiab],
			"SumPDOurFees",  [SumPDOurFees],
			"PD_Fees_CoverageCounsel",  [PD_Fees_CoverageCounsel],
			"Incurred",  [Incurred]
    )

ORDER BY
    [Inception Date] ASC,
    [Date Notified] ASC,
    [Policy Period] ASC​

[v-pnaroju-msft]

Hi GeorgeColl,

We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi GeorgeColl,

Thank you for the follow up.

Based on my understanding, the error A single value for column Insured cannot be determined occurs because PATHCONTAINS expects a text or string expression, whereas AllPolicies[Insured] is being treated as a column reference that returns multiple values. In DAX, a scalar or single value is required in this context, not a column of values.

Please follow the approach below, which might help to resolve the issue:

1. Ensure that you pass row level values to PATHCONTAINS. This can be achieved by wrapping the column within a row iterator such as SELECTEDVALUE(), or by using it inside FILTER where the expression is evaluated per row.

   The revised code below uses SELECTEDVALUE() to provide a scalar value for [Insured] to PATHCONTAINS. This ensures that each row is evaluated correctly within the FILTER context.

   VAR _CA =
   CALCULATETABLE (
   VALUES ( 'XX'[Policy Period] ),
   PATHCONTAINS ( @insuredName, SELECTEDVALUE ( 'XX'[Insured Name Simplification] ) )
   )

   VAR __DS0FilterTable3 =
   FILTER (
   VALUES ( 'AllPolicies'[Policy Period] ),
   NOT ( 'AllPolicies'[Policy Period] IN _CA )
   && PATHCONTAINS ( @insuredName, SELECTEDVALUE ( 'AllPolicies'[Insured] ) )
   )

   RETURN
   __DS0FilterTable3
   
   

   We hope that the information provided will help resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric Community.

   Thank you.

[v-pnaroju-msft]

Hi GeorgeColl,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou @DataNinja777 for your response.

Hi GeorgeColl,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solution provided by @DataNinja777 to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[DataNinja777]

Hi @GeorgeColl ,

It sounds like you're running into a classic issue where a report works on your desktop but not in the Power BI Service. This almost always points to an environmental difference or a query that's hitting a performance limit in the cloud. The problem is likely your data gateway connection or a specific, resource-intensive part of your DAX query, particularly the EXCEPT function.

The main reason this happens is that your desktop and the Power BI Service are completely different environments. Your computer connects directly to the data source, using its own CPU and RAM. The Service, however, must use a data gateway to reach on-premises data, which can be a significant bottleneck if the gateway server is slow or overloaded. Furthermore, your report runs on a shared capacity in the cloud, which has resource limits and timeout settings that your desktop doesn't. If your query is too demanding, the Service will simply stop it to protect performance for others.

Your DAX query is well-structured using SUMMARIZECOLUMNS, but it contains a function that is famously expensive. The EXCEPT function is the most likely culprit for the performance issue. To figure out the difference between two tables, the DAX engine must first build and store both complete tables in memory before comparing them. If _Collapps and _CA are large, this step can consume a massive amount of memory and time, causing the query to time out in the Service's more constrained environment.

VAR __DS0FilterTable3 =
        EXCEPT (
	_Collapps,
	_CA)

To pinpoint the exact problem, you should first publish a temporary, simplified version of your report. Replace your complex query with something basic like EVALUATE TOPN(100, 'XX'). If that simple report also fails to load, the issue is 100% related to your gateway or data source connection. You'll need to check the gateway status and test the connection in the Power BI Service settings. If the simple report runs fine, then the problem is definitely the complexity of your DAX query.

If you confirm the query is the issue, focus on rewriting the EXCEPT logic. Instead of materializing two full tables, you can often achieve the same result more efficiently by using a filter condition. For instance, you could get a list of policy periods from the 'XX' table and then filter the 'AllPolicies' table to find the ones that are not in that list. This approach avoids the costly operation of building the second table just for the comparison.

-- This is a potential, more performant alternative to your EXCEPT logic
VAR _PoliciesInXX = VALUES('XX'[Policy Period])

VAR __DS0FilterTable3_Alternative = 
    FILTER(
        VALUES('AllPolicies'[Policy Period]),
        NOT('AllPolicies'[Policy Period] IN _PoliciesInXX)
    )

Best regards,

[GeorgeColl]

Thank you @DataNinja777 for the response I really appreciate the detailed explanations and I'm sorry I've only just been able to reply.

I have looked at implementing the revised logic, but I am having an issue. I am looking to filter table XX and the table AllPolicies by the @insuredName parameter, then giving all the values that were in AllPolicies but not in XX. 

When I try to do that using this code:

    VAR _CA = CALCULATETABLE(
	VALUES ( 'XX'[Policy Period] ),
	PATHCONTAINS ( @insuredName, 'XX'[Insured Name Simplification] )
)

    VAR __DS0FilterTable3 =
    FILTER(
        VALUES('AllPolicies'[Policy Period]),
        AND(
                NOT('AllPolicies'[Policy Period] IN _CA) 
                , PATHCONTAINS ( @insuredName, 'AllPolicies'[Insured] ) 
        )
    )

I get the following error instead:

TITLE: Power BI Report Builder
------------------------------

Query preparation failed.

------------------------------
ADDITIONAL INFORMATION:

Query (29, 48) A single value for column '<oii>Insured</oii>' in table '<oii>AllPolicies</oii>' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result. (Microsoft Analysis Services)

Where, for clarification, row 29 is:  ", PATHCONTAINS ( @insuredName, 'AllPolicies'[Insured] ) " in __DS0FilterTable3. I'm not sure why it's saying about a single value, as I thought that wasn't required for PATHCONTAINS? So I am a bit confused here