Help Optimizing DAX Measures

[tyleharris]

Good afternoon all,

I'm working on a project that requires me to calculate the yearly totals of sales amount, sales quantity, 9L cases, and total transactions for a long list of customers IDs. However, the caveat is finding their first purchase date, adding 31 days, and starting from there. I want to essentially ignore their first month worth of sales.

My measures work has intended, and I get the results I need, but they are extremely slow when I scale it up and plug the long list of Customer ID's into the filter list. Is there a better way to write these measures to speed it up or is it just going to be inherently slow because I’m looking at massive amount of transactions over several years?

Could it also be an issue that I have one measure that does the calculation, then another that calls and forces the Grand total line to sum correctly? I should also note, that I have 4 sets of measure like the set below in the project - One for sales amount, sales quantity, 9L cases and transactions. They all have the same logic.

My first measure: - Does the calculation

(Adjusted) Sales Amount =
SUMX(

   VALUES(Licensee[Licensee ID]),

   CALCULATE(SUM('Sales Transactions'[Sales Amount Net]),

FILTER(ALL('Sales Transactions'[Transaction Date]), 'Sales Transactions'[Transaction Date] >= CALCULATE( MIN('Sales Transactions'[Transaction Date]), ALLSELECTED('Sales Transactions')) + 31)

   )

)

My second measure: - Forces the grand total to sum properly.

Adjusted Sales Amount =

IF(COUNTROWS(VALUES(Licensee[Licensee ID])) = 1,

   [(Adjusted) Sales Amount],

SUMX(VALUES(Licensee[Licensee ID]), [(Adjusted) Sales Amount]))

Snapshot of my layout - Top table is the working DAX measures and the two bottom tables are hard filtered and are the expected values.

Thank you!

[Anonymous]

What's your data model look like?  Happen to have a data table?  Looks like you do not as you have this :

..
FILTER (
            ALL ( 'Sales Transactions'[Transaction Date] ),

I'm assuming this is your Fact table. You really dont want to filter (i.e. iterate) a fact table because you will see a performance hit. 

Take a look at this post where someone had the same issue

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Is-there-a-faster-Average/m-p/779453#M4079

[tyleharris]

@Anonymous,

I unfortunately have no idea what my data model looks like. I'm an end user with a live connection so I can't see what the relationships look like.

Do you mean a date table or data table, as in the datatable() function? If date table, I have a calendar table, but when I try to use that instead of the transaction table it doesn't return the correct values.