Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Power BI Desktop | DAX Tuning

 I have the below DAX expression which is very slow during run time. I need in optimising and re-writting the DAX frmula so that it does not create much impact on FE and SE in Dax studio.

 

DEFINE
VAR __DS0FilterTable =
TREATAS ( { "France" }, 'DIM_GEO_HIERARCHY'[REGION_NAME] )
VAR __DS0FilterTable1 =
TREATAS ( { "France" }, 'DIM_GEO_HIERARCHY'[COUNTRY_NAME] )
VAR __DS0FilterTable2 =
TREATAS ( { 2023 }, 'DIM_CALENDAR'[YEAR] )
VAR __DS0FilterTable3 =
TREATAS ( { "6800019284" }, 'FCT_ILI'[ILI__INVOICE_NUMBER] )
VAR __DS0FilterTable4 =
TREATAS ( { "France" }, 'DIM_GEO_HIERARCHY'[MCO_NAME] )
VAR __DS0FilterTable5 =
TREATAS ( { "MnS" }, 'DIM_MANAGEMENTCENTER_HIER'[GBUGSF] )
VAR __DS0FilterTable6 =
TREATAS ( { "May" }, 'DIM_CALENDAR'[MONTH_NAME] )

VAR __DS0Core =
SUMMARIZECOLUMNS (
'DIM_CALENDAR'[YEAR],
'DIM_CALENDAR'[MONTH_NAME],
'DIM_GEO_HIERARCHY'[REGION_NAME],
'DIM_GEO_HIERARCHY'[MCO_NAME],
'DIM_GEO_HIERARCHY'[COUNTRY_NAME],
'DIM_MANAGEMENTCENTER_HIER'[MANAGEMENTCENTER_NAME],
'DIM_MANAGEMENTCENTER_HIER'[GBUGSF],
'DIM_MANAGEMENTCENTER_HIER'[DIVISION_NAME],
'DIM_COMPANYCODE'[DESCRIPTION],
'DIM_COMPANYCODE'[CODE],
'DIM_COMPANYCODE'[FK_BU_GEOGRAPHY_ID],
'DIM_GENERALLEDGERACCOUNT'[GL_ACCOUNT_GROUP],
'DIM_PROFITCENTER'[CONTROLLING_AREA],
'DIM_SUPPLIERS'[SUP__CODE],
'DIM_SUPPLIERS'[SUP__COMPILED_NAME],
'DIM_SUPPLIERS'[SUP__NAME_TRANSLATED],
'DIM_SUPPLIERS'[SUP_MC__CODE],
'DIM_SUPPLIERS'[SUP__COMPILED_ACTIVE_SUPPLIER_CODE_DISPLAY],
'DIM_SUPPLIERS'[SUP__COMPILED_ACTIVE_SUPPLIER_NAME],
'DIM_SUPPLIERS'[FK_SUP__COMPILED_HACAT_ID],
'DIM_HACAT_HIERARCHY'[HACAT_ID],
'DIM_HACAT_HIERARCHY'[HACAT_NAME],
'DIM_HACAT_HIERARCHY'[DOMAIN_NAME],
'DIM_HACAT_HIERARCHY'[CATEGORY_NAME],
'DIM_HACAT_HIERARCHY'[SUBCATEGORY_NAME],
'FCT_ILI'[ILI__ERP],
'FCT_ILI'[ILI__INVOICE_NUMBER],
'FCT_ILI'[ILI__POSTING_DATE],
'FCT_ILI'[ILI__COMPILED_LINE_DESC],
'FCT_ILI'[FK_ILI__COMPILED_MGCT_ID],
'FCT_ILI'[FK_ILI__COMPILED_HACAT_ID],
'FCT_ILI'[ILI__COMPILED_PO_NUMBER],
'FCT_ILI'[ILI__QTY_UOM],
'FCT_ILI'[ILI__QTY_VAL],

__DS0FilterTable,
__DS0FilterTable1,
__DS0FilterTable2,

__DS0FilterTable4,



"Spend",[Spend]
)

EVALUATE
__DS0Core

 

FE and SE status of DAX studio when I run this query. 

FE : 27,0000

SE: 800

 

Can someone suggest a  better way to re-write this query to improve the report perfomance. 

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Very easy - eliminate all the columns you don't need as well as all the measures you don't need. 🙂

     

    In DAX Studio you can expand measures to see the underlying code.  That will show you which of the measures is to blame. Or you can remove all measures, and then add them back in one by one while watching the timing.

     

    What's the cardinality of your dimension tables?  What other tables sit behind them?