Forum Discussion
Measure extremely slow in matrix
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.
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)))
2 Replies
- Greg_Deckler
Community Champion
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)))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!
