out of memory with dax

[reynaldo_malave]

Hi Guys,

I am running out of memory with the following expression. I must say it works if I use the function All as a filter on the calculate function of the first variable.

# Piezas 30 dias = 

VAR ultimafecha =

    CALCULATE(
        MAX( Ventas[Fecha] )        
    )

VAR resultado =

    CALCULATE(
        [# Piezas],
        DATESINPERIOD ( Calendario[Fecha], ultimafecha, -30, DAY )
    )

return 
    resultado

This is the first time this error happens to me. Any help would be grately appreciated

Thanks,

Reynaldo

[bcdobbs]

Can you share the DAX for [# Piezas]?

[reynaldo_malave]

I have tried with and without the calculate

# Piezas = 
// total de piezas vendidas, si no hay ventas entonces es blank

    Calculate( SUM( Ventas[Piezas] ) )

[bcdobbs]

Can you comment out the second variable and just return the first?

# Piezas 30 dias = 

VAR ultimafecha =

    CALCULATE(
        MAX( Ventas[Fecha] )        
    )

RETURN ultimafecha

Does that give the same error?

[reynaldo_malave]

I did. It returns the maximum date of the sales table with the current context filter

[bcdobbs]

is this running in power bi desktop?

is the dataset imported or direct query.

I can't see anything wrong with your dax. Are you able to share acsanitised pbix file?

[reynaldo_malave]

This is running on the desktop. Is imported. Cant share data but is 75 Million rows

[bcdobbs]

Have you tried deploying to power bi service and seeing how that it will run there?

What data type is the column you're summing? Worth trying to switch it to fixed decimal.

Beyond that would have to get into dax studio and see what is happening with your data model memory wise. Eg do you have lots of calculated columns?

[reynaldo_malave]

@bcdobbs  thanks for the help. Data type is whole number. Yep I publish it but measure is marked as error. There are non calculated columns its coming straight out of the DB. I will take a look at it on DAX Studio or will open a ticket with microsoft support.

Once again thanks

[bcdobbs]

Sorry I've not been more help; they're very difficult problems to solve remotely.

When you say the measure is marked with an error does it give any more details?

The only other thing to ask is how is your model structured? It should be able to cope easily with your measure even with 75M rows so I'm wondering if you have a proper star schema?

If you do look at it in dax studio go Advanced >> View Metrics and see if there are any columns that have high cardinality that you don't actually use: