Forum Discussion

PJStevens's avatar
PJStevens
Advocate II
2 years ago

Help optimizing calculated column in Power BI Desktop

Hello,

I created a calculated column that I thought wouldnt be overly complex, but it seems to be taking ages to load over "large" datasets (400k+ rows). I tried to split it up into several calculated columns/measures but it didn't improve the overall performance. I also tried to execute it in Power Query, but was unable to create the same calculation within PQ. In some cases, ive had to wait 1+ hour for it to calculate over the whole dataset.

 

Any tips is greatly appreciated:

Onderbreking =
    var StoringsDuur = 30/1440
    var CurrenDateTime = 'Leveringszekerheid MI'[DateTimeValue]
    var NRowsInRange = ROUNDUP((StoringsDuur*1440)/4,0)
    var Summed = CALCULATE(
                    (SUM('Leveringszekerheid MI'[Counter Onderbreking]))/NRowsInRange,
                    FILTER(
                        ALL('Leveringszekerheid MI'),
                        'Leveringszekerheid MI'[DateTimeValue] <= CurrenDateTime && 'Leveringszekerheid MI'[DateTimeValue] > CurrenDateTime - StoringsDuur
                        )
                    )
return
    IF(Summed = 1,1,0)

For clarification: DateTimeValue is just the DateTimeKey turned into decimals. Counter Onderbreking is identifying how often our heating curve is under a certain value (this is pre-calculated off imported data columns).

This formula is calculating how often there is an interruption in our network (or in other words, the % of how often we deliver without interruptions).

Thanks in advance!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PJStevens ,

    Use in function Remove all filters from the ‘Leveringszekerheid MI’ table, this can be very resource intensive on large datasets.

    You can also use the performance analyzer on the desktop to check which DAX is being used longer.

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • PJStevens's avatar
      PJStevens
      Advocate II
      2 years ago

      Hello,

       

      Thanks for the response!

       

      I'm not sure what you mean with "Use in function Remove all filters from the ‘Leveringszekerheid MI’ table, this can be very resource intensive on large datasets.", how would I incorporate this into my formula?

       

      I will use the performance analyzer to see what is causing it to take so long.


      Thanks for the response