Memory Problems

[Anonymous]

Hi dear Community,

I have a transactions table with more than 2M rows. 

This table has 6 columns:

DateTime

Client

Units

Cost

Type of Transaction

Campaign

The "Types of Transactions" are 3:
-Exchange
-Redemption
-Accumulation 

Only the first two types of transactions have an associated campaign.

What I would like to know is:
-No. of Exchanges per Campaign
-Number of Redemptions per campaign
-How many times a customer comes after doing a redemption
-How many units a customer buys after the first redemption

The way I found was through a calculated table:

Units_After_First_Redemption =
ADDCOLUMNS(   
        addcolumns(
            summarize(
                FILTER(
                    'Transactions',
                    'Transactions'[Campaign] <> Blank()
                    ), 
            'Transacciones'[Client], 
            'Transacciones'[Campaign] ),
        "Exchange", [Date First Exchange],
        "Redemption", [Date First Redemption] ,
    ),
    'Transactions', 
    CALCULATE( 
        COUNTROWS( 'Transactions'),
            FILTER(
                ALLEXCEPT('Transactions', 'Transacciones'[Client]),
                [Redemption] <> BLANK() &&
                'Transactions'[Type of Transaction] = "Accumulation" &&
                'Transactions'[DateTime] >[Redemption] + time(0,10,0)
                )
            ),
    "Units",
    CALCULATE(
        SUM( 'Transactions'[Units] ),
        FILTER(
                ALLEXCEPT('Transactions', 'Transacciones'[Client]),
                [Redemption] <> BLANK() &&
                'Transactions'[Type of Transaction] = "Accumulation" &&
                'Transactions'[DateTime] >[Redemption] + time(0,10,0)
                )
      )        
    )

But this gives an error of not enough memory, so I was thinking of doing this in power query, since this will be fixed. It will only vary when new transactions are added.

Any idea how it will be possible to do this?

Thank you very much,

Simão

[Anonymous · Accepted solution]

Hello guys,

I found the solution in this link:
https://community.powerbi.com/t5/Power-Query/SUM-IFS-in-Power-Query-using-a-date-and-article-number-...

The performance is still not the best, but as I have made it into a DataFlow it doesn't affect the dashboard's performance.

[v-easonf-msft]

Hi, @Anonymous 

Plesae provide sanitized sample data that fully covers your issue and related measure formulas.

This will help us better understand the problem.

Best Regards,
Community Support Team _ Eason

[croberts21]

Take a look at this: https://blog.crossjoin.co.uk/2020/01/20/visual-has-exceeded-the-available-resources-error-power-bi/

It looks like memory limits can only be raised in PBI Premium.