DAX formula optimization question

[CLNersesian]

Hello all, 

I'm setting up some DAX measures to calculate the number of days late for delivered goods. The following formula is an example (I've expanded some of the terms to make it easier to understand). The formulas work in Power BI, however, they're quite slow when using Direct Query. I'm wonding if anyone can suggest anything that might optimize the formulas so that they run faster. 

Late Delivery 45 days = IF('Sales Transaction'[Days Requested v. Ship Date] >=45, "Days Late (45+)", "N/A")

Count Late 45+ = CALCULATE(COUNTROWS('Sales Transaction'), FILTER(ALL('Sales Transaction'), 'Sales Transaction'[Late Delivery 45 days] = "Days Late (45+)"))

Thanks in advance for your help.

[Sean · Accepted solution]

@CLNersesian try without calculate

Measure = COUNTROWS ( FILTER ('Sales Transaction', 'Sales Transaction'[Late Delivery 45 Days]="Days Late (45+)" ) )

[MattAllington]

I'm with @Sean

[CLNersesian]

Thanks for the suggestions everyone. I was not able to use the forumla suggested by Sean because I do need to filter by a particular field. I think it's a performance issue that will needed be addressed at the service level. 

Thanks again.

[MattAllington]

How big is your transaction table?

Why are you using ALL(Sales Transaction) ?   This will slow the whole thing down - unless you have a reason for using ALL, just use the table instead

[CLNersesian]

Hi MAllnington,

When I remove the ALL() function I get the error I mentioned in other posts about True/False expression. Adding the ALL() and FILTER() functions cleared the existing filter and I was able to get past the error message.

Do you know an alterntive way to write the formula that avoid ALL() and FILTER() because I know the row level filtering is slow. I just can't figure out how to write the formula without it.

Thanks,

C.

[CLNersesian]

The transaction table has ~500,000 rows - and will progressively get larger. At the moment, I have two formulas set up - one that identifies a range of days between requested and actual ship dates, and assigns a text statement (e.g., <15 days, ontime etc.). The next one counts the number of rows with the statement. 

1) Late Delivery 30 days = IF('Sales Transaction'[Days Requested v. Ship Date] >=30 && 'Sales Transaction'[Days Requested v. Ship Date] <=44, "Days Late (30+)", "N/A")

2) Count Late 30+ = CALCULATE(COUNTROWS('Sales Transaction'), FILTER(ALL('Sales Transaction'), 'Sales Transaction'[Late Delivery 30 days] = "Days Late (30+)"))

After that other formulas calcualte off of these, such as% percentage of late/ontime shipments by category bins (15 days, 30 days etc.).

Originally, I had the first formula as a long IF statement for each bin, but it was too slow, so I split the IF statements.

This might not be the best way to do this - so if you have any suggestions, please let me know. Honestly, DAX is not my forte! 

In the meantime, thanks for your help so far!

[kcantor]

How about simplifying and dropping the text reference?

Days Late (45+) = CALCULATE(COUNTROWS('Sales Transaction'), [Days Requested v. Ship Date] > 45)

Days Late (30) = CALCULATE(COUNTROWS('Sales Transaction'), [Days Requested v. Ship Date] > 30 && [Days Requested v. Ship Date] < 45)

I definately am no expert. I don't even play one on TV . . .

[CLNersesian]

Thanks Kcantor - I get the same error message I mentioned above ("A function 'CALCULATE' has been used in a True/False expression taht is used as a table filter expression. This is not allowed.") using this formula. 

Ill play around with the filters and see if it's faster... more soon. 

[Anonymous]

While for the second formula you can use this measure:

Let me know if it works for you.

#I'M Not An Expert#

[CLNersesian]

Thanks for the suggetion B129, I originally tried that formula but kept getting an error: "A function 'CALCULATE' has been used in a True/False expression taht is used as a table filter expression. This is not allowed." 

This error lead me to adapting the formula as you saw. I think the error is related to how filter interactions relate in PBI - because it works fine in Excel. 

[SabineOussi]

I believe you don't need the second formula.

In order to get the count of how many shipments were late, you can put your Late Delivery 45 days field into a card visual and on the visual level filter, choose "Days Late (45+)".

[CLNersesian]

Thanks Sabine - good thought, but I was actually going to use the formulas for other calculations. Also, the first formula, even in the card box is quite slow using Direct Query. 

[Anonymous]

Hi @CLNersesian,

   for the first formula I can suggest you to use the DATEDIFF() function.

Something like this:

The formula is a Calculated Column.

#I'M Not An Expert#