Forum Discussion

SchuWaMax's avatar
SchuWaMax
Frequent Visitor
2 years ago

Calculate Max date column in composite model performance

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"

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • SchuWaMax's avatar
    SchuWaMax
    Frequent Visitor
    2 years ago

    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.