Visual runs out of memory in one report but runs properly in another report, using same DAX code

[Dpyr]

I have two different PBIX files, getting data from different databases, one from SQL server database and one from Azure SQL database. Both databases have similar structure except the first is on local server and the second on cloud.

I have created a measure calculating the sum of a column [Amount] using filters on 5 related tables.

When I put the measure on a visual with customers in report which uses the SQL Server Database, it runs properly.

When I put same measure on a visual with customers in report which uses the Azure SQL Database, it never ends running, ending with a memory error.

If it helps, the relationships the measure uses are:

1) 'DOC' 1:* 'LINES'

2) 'FP' 1:* 'DOC'

3) 'TRN' 1:* 'FP'

4) 'FTRN' 1:* 'DOC'

5) 'CUST' 1:* 'DOC'

'CUST' is the table containing customers.

Neither of relationships uses both direction.

The DAX code is like:

Sales Values =
CALCULATE (
    CALCULATE (
        CALCULATE (
            CALCULATE (
                CALCULATE (
                    CALCULATE ( SUM ( 'LINES'[Amount] ), FILTER ( 'TRN', TRN[COL1] = 1 ) )
                        - CALCULATE ( SUM ( 'LINES'[Amount] ), FILTER ( 'TRN', TRN[COL1] = -1 ) )
                        + CALCULATE ( BLANK (), FILTER ( 'TRN', TRN[COL1] = 0 ) ),
                    FILTER ( 'LINES', 'LINES'[Amount] <> 0 )
                )
            ),
            FILTER ( 'DOC', 'DOC'[ISCANCEL] <> 1 )
        ),
        FILTER (
            'FTRN',
            NOT ( 'FTRN'[TFPRMS] IN { 100, 105, 106 } )
                && 'FTRN'[SR] = 2222
        )
    ),
    FILTER ( 'FP', ISBLANK ( 'FP'[FCOL] ) = FALSE () )
)

Why may this is happening?

[HotChilli · Accepted solution]

Are you using import mode? If so, it's probably down to the different data sizes (I assume they are different db's) in the 2.

In my opinion, that DAX needs re-written regardless (too complex, too many context transitions) and I wonder what the data model looks like too

[v-robertq-msft · Accepted solution]

Hi, @Dpyr 

According to your description, you can query the data properly when connecting to the SQL Server database on-premises, but you can’t get any data when you use the Azure cloud database. Right?

If you used the “Import” mode to connect with them in the Desktop, I think the reason is explained by HotChilli: About complex DAX formulas and data models.

If you can’t query the data in the Power BI service, I think you can first go to the Gateway setting to open the “Allow cloud data source to refresh through this gateway cluster” option, like this:

More info about this option

Best Regards,

Community Support Team _Robert Qin

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

[HotChilli · Accepted solution]

Are you using import mode? If so, it's probably down to the different data sizes (I assume they are different db's) in the 2.

In my opinion, that DAX needs re-written regardless (too complex, too many context transitions) and I wonder what the data model looks like too