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
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

speedramps's avatar
speedramps
Icon for Super User rankSuper User
4 years ago

Hi again Elorien

 

You can create this measure.

Then create a table visual with ticket id and the measure. Then apply a filter for event-id = 50. It should run quickly ...

 

Youranswer =
 
VAR nonworkdays =
FILTER(Calendar,
Calendar[workday] = 0,
Calender[date] >=
SELECTEDVALUE(Tickets[startdate]),
Calendar[date] <=
SELECTEDVALUE(Tickets[enddate])
)
 
RETURN 
COUNTROWS(nonworkdays)