0

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

DAX Query

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 Without filter (storage engine)

With filter (Query Engine kicks in) With filter (Query Engine kicks in)

Adding the tables relationship as a diagram Tables relations

Eric Mamet
  • 2,681
  • 2
  • 13
  • 43
  • can you post your data model diagram that shows all relevant tables and their relations? – RADO Mar 17 '21 at 22:19
  • I just discovered that I can apparently solve my speed issue by replacing my SUMX(?, IF(...)) by a CALCULATE(SUM(), FILTER) as this is entirely resoved by the Storage engine... CALCULATE(SUM('Fact'[RAW_SALES_UNIT]), 'Fact'[FREQUENCY] == FrequencyOption) But I would still like to understand the difference in behaviour between Version B & C... – Eric Mamet Mar 17 '21 at 22:26
  • 1
    I've run into [similar issues](https://stackoverflow.com/questions/61805513). It seems like the internal optimization engine needs improvement. – Alexis Olson Mar 18 '21 at 14:08
  • @RADO I don't see how to attach files... – Eric Mamet Mar 18 '21 at 16:43
  • I think the issue is caused by bidirectional relations - there is no reason to have them in this model. Try to change them to the normal 1 to many and see if there is a difference. In general, avoid bi-directional relations at all costs. – RADO Mar 18 '21 at 17:47

1 Answers1

0

In this particular case, it looks like I can solve my performance problem by "rephrasing" using CALCULATE() and a simple filter.

CALCULATE() option runs far better

I have no explanation for the major speed difference between Versions B & C but version D performs very well with or without the filter

Eric Mamet
  • 2,681
  • 2
  • 13
  • 43
  • The speed difference is most likely due to the bi-directional connections - see my comment to your question. – RADO Mar 18 '21 at 17:49
  • I inherited this model and I had heard to try and avoid bi-directional relations... What are they achieving? Is it cross filter between dimensions? However, the difference between B & C is just that I replaced a constant by a variable... I'll try getting rid of them anyway. – Eric Mamet Mar 18 '21 at 20:35
  • 1
    The difference is that when you replaced a constant with a measure you've involved another table into the calculation. Because you have bi-directional relations, you forced the filter to go through the fact table with millions of records, which is very slow. In addition, by-directional relations in Power BI are implemented differently from the normal 1:M relations and are much less efficient. – RADO Mar 18 '21 at 21:56
  • You can learn more about this issue here: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/ – RADO Mar 18 '21 at 22:00
  • Many thanks. Much appreciated (PS: I have got rid of all bi-directional relations). If you could answer my original query with your comment above, I would flag it as the best answer – Eric Mamet Mar 19 '21 at 10:58