cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!