cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dpyr
Frequent Visitor

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

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?

2 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

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

View solution in original post

v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1614753557719.png

v-robertq-msft_1-1614753557735.png

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.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1614753557719.png

v-robertq-msft_1-1614753557735.png

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
Super User
Super User

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors