I have a relatively simple DAX query which becomes very slow if I change my SUM(...) into SUMX(...).
The main measure has three versions
- Version A: CALCULATE(SUM(...))
- Version B: SUMX(..., IF some_column = constant, other_column)
- Version C: SUMX(..., IF some_column = variable, other_column)
Finally, I have a FILTER option
Version A is always quick, with or without filter with all work done in storage engine
Version B is pretty quick too, also using the storage engine mostly
Version C is slow IF I add a filter and I don't get why it behaves differently from B, or why the filter has such a bad effect on it.
It uses a variable instead of a constant in the SUMX() and that seems to get resolved by the query engine, hence big delays. In my query context, the variable always evaluates to "Monthly".
In particular it becomes very slow if I add a filter to my query.
Can someone explains why B & C behave so differently? How could I get B's performance with a variable instead of the constant? Could I use a filter somehow to avoid the Query Engine kicking in?
I join screenshots of the Version C queries that use a lot of query engine when I add the filter
Without filter -> Storage Engine
With filter (Query Engine kicks in)
Adding the tables relationship as a diagram
