Forum Discussion

Elorian's avatar
Elorian
Resolver I
4 years ago
Solved

Performance issue with a DAX formula

Hello,

My case is the following :

1) I have a big history table of all events occuring on the tickets of my DB, basically with the type of the event, the start date and the end date. The only event type interesting me is "EVENT_ID = 50" (to explain why it appears below).

This table is more than 1100000 lines today, about the half of which are of EVENT_ID = 50

2) I have a classical Time Table built on a day by day interval, with several info columns as usual for that kind of table and in which I have a supplementary calculated column telling if that day if a working day or not (working day = 1, non-working day = 0)

This table is 1827 lines today

 

What I try to do is to compute, for each event of the type 50 of the history table, the number of non-working days in between the start date and the end date of the event. To do so, I used the following formula that seems working :

 

+Diff_Time (days_ooo) =

VAR daysooo =
IF(
HISTORY[EVENT_ID] = 50,
CALCULATE(
COUNTROWS(_TimeTable),
FILTER(_TimeTable,
_TimeTable[Date] >= DATE(YEAR(HISTORY[+Prev History_ChgDate per request]),MONTH(HISTORY[+Prev History_ChgDate per request]),DAY(HISTORY[+Prev History_ChgDate per request])) &&
_TimeTable[Date] <= DATE(YEAR(HISTORY[CHANGED_DATE]),MONTH(HISTORY[CHANGED_DATE]),DAY(HISTORY[CHANGED_DATE])) &&
_TimeTable[OpenDaysFR] = 0
)
),
BLANK()
)

RETURN
 
IF(
HISTORY[EVENT_ID] = 50,
daysooo,
BLANK()
)
 
My (big) problem is that since I put that formula in place, I have super bad Power BI desktop performances, even at the opening the report. Adding a new column to the history table takes tenths of minutes, and it's taking a lot of memory (the windows SQL analyzer, apparently).
 
I thus assume that my request is not efficient at all and should be improved, but I don't know at all how. I tried multiple other possibilities, but it's the only one not making me run out of memory (the last version was with the condition "open day = 0" out of the FILTER, and it ran out of memory, while in the FILTER, it works (I don't know why, but it is so).
 
Does someone has a good idea on the Problem and how to improve the performance?
 
Thanks in advance,
Elorian

 

Need Help
  • Elorian's avatar
    Elorian
    4 years ago

    Hello,

    The solution in itself is not correct, because your FILTER argument is not 1 expression and it creates an error.

    Nevertheless, based on your idea, the following formula seems to work at least better :

     

    +Diff_Time (days_ooo) =

    VAR daysooo =
     
    FILTER(_TimeTable,
    _TimeTable[OpenDaysFR] = 0 &&
    _TimeTable[Date] >= DATE(YEAR(HISTORY[+Prev History_ChgDate per request]),MONTH(HISTORY[+Prev History_ChgDate per request]),DAY(HISTORY[+Prev History_ChgDate per request])) &&
    _TimeTable[Date] <= DATE(YEAR(HISTORY[CHANGED_DATE]),MONTH(HISTORY[CHANGED_DATE]),DAY(HISTORY[CHANGED_DATE]))
    )

    RETURN
     
    IF(
    HISTORY[EVENT_ID] = 50,
    COUNTROWS(daysooo),
    BLANK()
    )
     
    Kr,
    Elorian

11 Replies

    • speedramps's avatar
      speedramps
      Super User
      4 years ago

      Hi Elorian

       

      Please confirm I have understood.

      You have a historty table like this

      Ticket Event_ID Startdate Enddate
      1 50 01/01/2021 08/01/2021
      8 60 03/01/2021 13/01/2021
      12 70 05/01/2021 14/01/2021
      21 50 08/01/2021 16/01/2021
      28 72 17/01/2021 19/01/2021
      33 73 22/01/2021 21/01/2021
      39 50 31/01/2021 26/01/2021
      48 75 03/02/2021 29/01/2021
      54 76 10/02/2021 02/02/2021

       

      A calnedra tbale like this

      Date Working day
      01/01/2021 0
      02/01/2021 0
      03/01/2021 0
      04/01/2021 1
      05/01/2021 1
      06/01/2021 1

       

       

      And you wnat a rpwort like this for Event-ID 50 only tickets ?

      Ticket Event_ID Startdate Enddate Working days
      1 50 01/01/2021 08/01/2021 4
      21 50 08/01/2021 16/01/2021 5
      39 50 31/01/2021 26/01/2021 10
      • Elorian's avatar
        Elorian
        Resolver I
        4 years ago

        Hi speedramps,

        In a simplify way, yes, that's that (except I don't count the working days but the non-working days).

        Kr,

        Elorian

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    OMG ... the portal has screwed up my example data ... grhhh ðŸ˜«

     

  • Vashisth12's avatar
    Vashisth12
    New Member
    4 years ago

    I have similar problem with that please can you connect and solve for me as well