Performance improvement with Summarize

[Anonymous]

Hi All,

I have below DAX which is used to calculate differect result but by using Summarize function , performance is affecting badly. Please help me to write below DAX in optimized way so that these DAX works fastly.

All = COUNTX(ADDCOLUMNS(SUMMARIZE(' Sales',' Sales'[Material],'Geography'[Country]),"counts",CALCULATE(DISTINCTCOUNT('Sales'[Material]))),[counts])

#2  = SUMX(ADDCOLUMNS(SUMMARIZE('Sales','Geography'[Country], 'Sales'[Material]),"fulldata", CALCULATE([All], FILTER(ADDCOLUMNS(SUMMARIZE('Sales','Sales'[Code],' Sales'[Material],'Geography'[Country]), "weightsum", CALCULATE(SUM('Sales'[Weight]))), [weightsum]>0))),[fulldata])

#3 = VAR Blanks = CALCULATETABLE(VALUES('Sales'[Material]),ALL('Sales'),ISBLANK('Sales'[Weight]),'Sales'[Weight]=0)
RETURN
CALCULATE([#2],KEEPFILTERS (NOT 'Sales'[Material] IN (Blanks)))

Thanks in Advance !

Vinay

[v-eachen-msft]

Hi @Anonymous ,

Instead of using the SUMMARIZE() function in this manner, you could use the SUMMARIZECOLUMNS() DAX function. The SUMMARIZECOLUMNS() function is newer and better optimized.

[Anonymous]

@v-eachen-msft 

Summarizecolumn cannot be used as a Measure right? it can only be used to create a Table, here in my situation i'm Summarize as a Measure. Please correct me if i'm wrong

If its possible to have a Measure with Summarizecolumn, can you please provide one example ?

Regards,

Vinay

[jthomson]

Might be an idea to give us some sample data, and what you're actually trying to get the measures to do