Forum Discussion
DAX formula optimization question
CLNersesian try without calculate
Measure = COUNTROWS ( FILTER ('Sales Transaction', 'Sales Transaction'[Late Delivery 45 Days]="Days Late (45+)" ) )
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
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!
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 . . .
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.
