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...
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
speedramps's avatar
speedramps
Super User
4 years ago

removed post due to data coruption

  • 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

        So you want a report with the number of non working days per ticket. But only show event-id =50 tickets on the report???