Calculate Max date column in composite model performance

[SchuWaMax]

Hello,

I can't understand the reason why I have such a bad performance. 

Constellation:

- One Dataset in Power BI Service with direct query with 5mb (only one relevant table screenshoted)
- Local model with a new date table 

For some reason the calculation runs out of memory (RAM is not the problem). When I try to calculate the max from the ordenary calender table it works fine. 

Do you have any idea to solve my problem?

Date table in Power Query:
let
Quelle = {Number.From(#date(2022,01,01))..Number.From(Date.EndOfYear(Date.From(DateTimeZone.UtcNow())))},
#"In Tabelle konvertiert" = Table.FromList(Quelle, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Geänderter Typ" = Table.TransformColumnTypes(#"In Tabelle konvertiert",{{"Column1", type date}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"Column1", "Datum"}})
in
#"Umbenannte Spalten"

[SchuWaMax]

Hi @Anonymous ,
thank for your answer. 

The only difference is SUM instead of SUMX, right? Also used calculate in my measure. I tried your solution and didn't work, sorry.

[Anonymous]

Hi @SchuWaMax ,

Base on your description, it sounds like you're experiencing some performance issues with your Power BI composite model. You can update the formula of your measure as below and check if it can work well:

TEST max date =
CALCULATE (
    SUM ( 'DirectQuery facts'[Kosten] ),
    FILTER (
        'DirectQuery facts',
        'DirectQuery facts'[arbdatum] <= MAX ( 'New local dates'[Datum] )
    )
)

 Note that this measure uses the CALCULATE function instead of the SUMX function, which can be more efficient when working with large datasets. Additionally, the MAX function is only used once in the measure, which can help reduce the amount of iteration required.

In addition, you can refer the following links to optimize your DAX formula:

Video:

[DAX] Best Practices 101 for Optimization & Performance (with Alberto Ferrari) 

DAX Optimizations Examples with Alberto Ferrari 

Blog:

DAX Optimization Examples

How to Use DAX Studio to Optimize DAX Code 

Best Regards