DAX Query Performance Issue Help

[Anonymous]

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

[bvilten · Accepted solution]

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.

[Anonymous]

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

Thanks for your inputs.