topic Re: DAX Query Performance Issue Help in DAX Commands and Tips

DAX Query Performance Issue Help

Anonymous — Wed, 26 May 2021 12:41:59 GMT

Hi Team,

Below is my DAX measure and its taking almost 26 sec to return the data, please help to optimize

MEASURE 'Cntrl ETLControlTable'[ShippedTenderedCountNew1] = (/* USER DAX BEGIN */ var _getdate= MAX(Inv_Dim_LinkDate_FactInv[DimLinkDate]) return CALCULATE ( DISTINCTCOUNT(Inv_Fact_Inventory[CURINV_CARGO_ID] ) ,Inv_Fact_Inventory[Dashboard] = "Current Inventory NEW" ,Inv_Fact_Inventory[Inv_LinkDate] < _getdate ,Inv_Fact_Inventory[Ship Datee] >= _getdate ,REMOVEFILTERS(Inv_Dim_LinkDate_FactInv) )

Above measure plus there are four more default filters applied as (from filter pane) and i'm trying to fetch the result for Rail Head 004

VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('Inv_Fact_Inventory'[CARRIER TYPE])), NOT('Inv_Fact_Inventory'[CARRIER TYPE] IN {BLANK()}) ) VAR __DS0FilterTable2 = FILTER( KEEPFILTERS(VALUES('Inv_Dim_LinkDate_FactInv'[DimLinkDate])), NOT(ISBLANK('Inv_Dim_LinkDate_FactInv'[DimLinkDate])) ) VAR __DS0FilterTable3 = TREATAS({14}, 'vNoDays'[vNoDays]) VAR __DS0FilterTable4 = TREATAS({"Shipped Inventory"}, 'Inv_Fact_Inventory'[Dashboard]) VAR __DS0FilterTable5 = TREATAS({"004"}, 'Inv_Fact_Inventory'[Rail Head])

Here is the statistics capture from server

Please help me to fix the performance issue

Re: DAX Query Performance Issue Help

bvilten — Wed, 26 May 2021 23:09:22 GMT

So we can't really tell much about the query structure with out the full picture of the server timing window.

how many SQL subclass are being generated?

do you have CALLBACKS? etc
from what I can see you are spending way too much time in the formula engine. How big and how many columns is the Inv_Fact_Inventory table? if it is large you could consider building a temp table on the fly with only the columns you need pulling in specific columns as necessary from related tables. then write your queries to hit that temp table.

Re: DAX Query Performance Issue Help

Anonymous — Tue, 01 Jun 2021 10:25:25 GMT

Building temp table with required column helped to achieve desired performance.

Thanks for your inputs.