Forum Discussion

Elorian's avatar
Elorian
Icon for Resolver I rankResolver 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
Icon for Super User rankSuper User
4 years ago

removed post due to data coruption

speedramps's avatar
speedramps
Icon for Super User rankSuper 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
    Icon for Resolver I rankResolver 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
      Icon for Super User rankSuper 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???

      • Elorian's avatar
        Elorian
        Icon for Resolver I rankResolver I
        4 years ago

        No, not on a report... I want a calculated column with that number of non working day for all tickets with event-id 50, but with the size of tables I mentionned in my initial post. The formula I'm using (see post) is working, but apparently it ruins the performance of Power BI afterwards --> want to know if my formula is 1) correct and 2)  can be improved to not kill performances