The ALL DAX function is very slow.

[ncbshiva]

Hi Team,

I want to calculate some Average Price based on some logic and below is the DAX expression for that.

Average Price = CALCULATE(DIVIDE([Dynamic Compare To];[FP&L Volume CompareTo];0);
ALL('AEM Responsibility'[AEM Group];'AEM Responsibility'[AEM Responsibility]);ALL(Market[Market Name]);ALL('Sold-To Party (BV)'[Sold-To Party (BV)]);ALL('Ship-To Party (BV)'[Ship-To Party (BV)]);ALL(Brand[Brand]);ALL('Specific Product'[Specific Product(BV)]))

Now if want to create a table with all the above mentioned dimensions along with the Average price it takes very long time to load the table.

Below is the DAX query generated by Power BI :

VAR __DS0FilterTable11 =
TREATAS({"ABS"}, 'ABS/HL Switch'[Measure Name])

EVALUATE
TOPN(
502,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'AEM Responsibility'[AEM Group],
'AEM Responsibility'[AEM Responsibility],
'Sold-To Party (BV)'[Sold-To Party (BV)],
'Market'[Market Name]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__DS0FilterTable7,
__DS0FilterTable8,
__DS0FilterTable9,
__DS0FilterTable10,
__DS0FilterTable11,
"Average_Price", 'KPI'[Average Price]
),
[IsGrandTotalRowTotal],
0,
'AEM Responsibility'[AEM Group],
1,
'AEM Responsibility'[AEM Responsibility],
1,
'Sold-To Party (BV)'[Sold-To Party (BV)],
1,
'Market'[Market Name],
1
)

ORDER BY
[IsGrandTotalRowTotal] DESC,
'AEM Responsibility'[AEM Group],
'AEM Responsibility'[AEM Responsibility],
'Sold-To Party (BV)'[Sold-To Party (BV)],
'Market'[Market Name]

Is there any way where i can optimize this DAX expression ? Please help me 

Regards

SHIVA 

[v-cherch-msft]

Hi @ncbshiva 

It seems you may try to use ALLEXCEPT function.

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

Regards,

[ncbshiva]

Hi @v-cherch-msft ,

Thanks for the reply , the problem with ALLEXCEPT is i cannot use more than 2 tables.

As you can see in my DAX  formula , i have included around 7 tables.

Please let me know how can i improve the speed for this DAX formula.

Regards

SHIVA