Performance issue with a DAX formula

[Elorian]

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

[Vashisth12]

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

[speedramps]

OMG ... the portal has screwed up my example data ... grhhh 😫

[speedramps]

removed post due to data coruption

[speedramps]

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]

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]

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]

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]

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]

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]

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 · Accepted solution]

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