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

    I will check that formula because I don't know well the SELECTEDVALUE function. But for the 1st part, no, because this calculated column that I want will serve for a further calculation, I really need it as a calculated column, not in a visual/table visual. Thanks!

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

      I am an unpaid power bi volunteer

       

      Please can you click Accept As Solution to give kudos and then  raise another ticket.

       

      Ive answered your question well. But you didnt mention it had to be a calculated field.  Thanks 

      😞

      • Elorian's avatar
        Elorian
        Icon for Resolver I rankResolver I
        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