Forum Discussion

krisstok's avatar
krisstok
Frequent Visitor
3 years ago
Solved

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 b...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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)))
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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)))
  • krisstok's avatar
    krisstok
    Frequent Visitor
    3 years ago

    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!