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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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 Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors