topic Re: Measure extremely slow in matrix in DAX Commands and Tips

Measure extremely slow in matrix

krisstok — Wed, 21 Sep 2022 22:23:19 GMT

Hi,

I need help optimization in dax measure.

I'm counting customers who had turnover greater than 10. The sales table has customers, document numbers, products, so I to group the turnover by customer and then filter the total above 10.

Here is the formula:

Customers Qty Turnover > 10 =
VAR _group =
ADDCOLUMNS (
VALUES ( Sales[CustomerNameGrouped] ),
"Turnover", [Turnover]
)
VAR _filter =
FILTER ( _group, [Turnover] > 10 )
VAR _result =
CALCULATE (
DISTINCTCOUNT(Sales[CustomerNameGrouped]),
_filter
)
RETURN _result

When I put this in matrix (customers in rows, months in columns), table is very slow. Performance analyzer shows me after scrolling that dax measure took more than 6 seconds. So I don't know how to perform it better.

Sales table has about 150k rows.

Re: Measure extremely slow in matrix

Greg_Deckler — Thu, 22 Sep 2022 00:47:46 GMT

krisstok Try this:

Customers Qty Turnover > 10 = VAR __Table = CALCULATETABLE('Sales',KEEPFILTERS(DISTINCT('Sales'[CustomerNameGrouped)),[Turnover] > 10) RETURN COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"__Group",[CustomerNameGrouped))) or maybe: Customers Qty Turnover > 10 = VAR __Table = FILTER(SUMMARIZE('Sales',[CustomerNameGrouped],"__Turnover",[Turnover]),[__Turnover] > 10) RETURN COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"__Group",[CustomerNameGrouped)))

Re: Measure extremely slow in matrix

krisstok — Thu, 22 Sep 2022 06:52:06 GMT

Hey, thanks for respond.

Second solution works, now performance analyzer shows 800ms :).

First solution doesn't, it says:

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Thanks for help!