Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sarthaks
Helper III
Helper III

DAX Query Performance Issue Help

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

sarthaks_0-1622032636427.png

 

Please help me to fix the performance issue

 

1 ACCEPTED SOLUTION
bvilten
Helper II
Helper II

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.

 

View solution in original post

2 REPLIES 2
bvilten
Helper II
Helper II

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.

 

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

 

Thanks for your inputs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors