Hello together,I have a question about custom measures. I have created a measure in my semantic model which is quite slow.The measure takes about 15 seconds. If I define the same measure as a custom measure in Power BI or DAX Studio, the measure takes only 1 second. Can anyone tell me what is the reason for this? Thanks

What semantic model are you referring to?

Sorry, it's a ssas tabular model 1500

Power BI is fundamentally pretty similar to SSAS under the hood but is updated more frequently. In the last couple of years, there have been some optimizations that could potentially make a large difference. As an example, the May 2020 update improved performance issues I explained here. The October 2021 update also included some optimizations. If I understand correctly, the latest major version of SSAS is for SQL Server 2019, although there have been "cumulative updates" within the last couple of months. This is all largely speculation since I don't actually know what your measures are and what versions of things you're running (and even if I did, I'd have to do some deep digging to isolate differences).

Credential When you say, the measure takes 15 seconds. How did you come up with that number? Is it when you use the measure inside the report?

I don't think it has anything to do with Power BI. I think it's more because of how the Query Optimizer optimizes the measures.Here is the query with a running time of 15 seconds: -- Semantic Model // DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('DIM.BI_Datum'[Datum])), 'DIM.BI_Datum'[Datum] < DATE(2021, 11, 16) ) VAR __DS0FilterTable2 = TREATAS({"19144"}, 'DIM.SAP_Personal_Daten__Besitzer'[Personalnummer_Master]) EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, __DS0FilterTable2, "Neukunde_DIV_SUM", IGNORE('FACT.Measure'[Neukunde_DIV_SUM]) ) Here is the query with a running time of 1 seconds: -- Custom Measure // DAX Query DEFINE MEASURE 'CUSTOM.Measure'[CM_Neukunde_DIV_SUM] = (/* USER DAX BEGIN */ VAR ST = LASTDATE('DIM.BI_Datum'[Datum]) RETURN CALCULATE( SUMX( SUMMARIZE( FILTER( 'FACT.TJ_Rechnungsausgangsjournal' , RELATED('DIM.TJ_Auftrag'[Erstellt]) >= CALCULATE('FACT.Measure'[Datum_VM_Relevant_Seit], 'DIM.BI_Datum'[Datum] <= ST) && RELATED('DIM.TJ_Rechnungsausgangsjournal'[Ist_Fakturiert]) = "J" ) ,'DIM.TJ_Auftrag'[Erstellt] ,'DIM.TJ_Unternehmen'[Nummer] ,'DIM.BI_Kostenstelle'[DIV_Kurzname] ,"IstNeukunde" ,IF( CALCULATE( 'FACT.Measure'[TJ_Leistungsnachweis_Umsatz_per_VM_SUM] ,ALLEXCEPT( 'FACT.TJ_Rechnungsausgangsjournal' ,'DIM.TJ_Unternehmen'[Nummer] ,'DIM.BI_Kostenstelle'[DIV_Kurzname] ) ,FILTER(DATUM_ALL, 'DIM.BI_Datum'[Datum] < 'DIM.TJ_Auftrag'[Erstellt]) ,KEEPFILTERS('FILTER.VM_Relevant'[Ist_VM_Relevant] = "N") ,KEEPFILTERS('FILTER.VM_Relevant_STO_GES_DIV'[Ist_VM_Relevant_STO_GES_DIV] = "N") ,KEEPFILTERS('FILTER.VM_Relevant_KST'[Ist_VM_Relevant_KST] = "N") ) = 0 && NOT CALCULATE( 'FACT.Measure'[TJ_Leistungsnachweis_Umsatz_per_VM_SUM] ,ALLEXCEPT( 'FACT.TJ_Rechnungsausgangsjournal' ,'DIM.TJ_Unternehmen'[Nummer] ) ,FILTER(DATUM_ALL, 'DIM.BI_Datum'[Datum] < 'DIM.TJ_Auftrag'[Erstellt]) ,KEEPFILTERS('FILTER.VM_Relevant'[Ist_VM_Relevant] = "N") ,KEEPFILTERS('FILTER.VM_Relevant_STO_GES_DIV'[Ist_VM_Relevant_STO_GES_DIV] = "N") ,KEEPFILTERS('FILTER.VM_Relevant_KST'[Ist_VM_Relevant_KST] = "N") ) = 0 , 1 ,BLANK() ) ) ,[IstNeukunde] ) ,USERELATIONSHIP('DIM.TJ_Auftrag'[Erstellt], 'DIM.BI_Datum'[Datum]) ) /* USER DAX END */) VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('DIM.BI_Datum'[Datum])), 'DIM.BI_Datum'[Datum] < DATE(2021, 11, 16) ) VAR __DS0FilterTable2 = TREATAS({"19144"}, 'DIM.SAP_Personal_Daten__Besitzer'[Personalnummer_Master]) EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, __DS0FilterTable2, "CM_Neukunde_DIV_SUM", IGNORE('CUSTOM.Measure'[CM_Neukunde_DIV_SUM]) ) 'CUSTOM.Measure'[CM_Neukunde_DIV_SUM] is exact the same as 'FACT.Measure'[Neukunde_DIV_SUM]

Performance semantic model measure vs custom measure

11 Replies

AlexisOlson
Super User
4 years ago
What semantic model are you referring to?
- Credential
  Frequent Visitor
  4 years ago
  Sorry, it's a ssas tabular model 1500
  - AlexisOlson
    Super User
    4 years ago
    Power BI is fundamentally pretty similar to SSAS under the hood but is updated more frequently. In the last couple of years, there have been some optimizations that could potentially make a large difference.
    
    As an example, the May 2020 update improved performance issues I explained here. The October 2021 update also included some optimizations.
    
    If I understand correctly, the latest major version of SSAS is for SQL Server 2019, although there have been "cumulative updates" within the last couple of months.
    
    This is all largely speculation since I don't actually know what your measures are and what versions of things you're running (and even if I did, I'd have to do some deep digging to isolate differences).
m3tr01d
Continued Contributor
4 years ago
Credential
When you say, the measure takes 15 seconds. How did you come up with that number?
Is it when you use the measure inside the report?

Forum Discussion

Performance semantic model measure vs custom measure

11 Replies

Related content

How to improve performance of Semantic model

Performance identifying Measure

Would Enabling Large Semantic Model Storage Format Improve Refresh Performance?

Performance Boost repetitive Measures

DAX Query Performance vs Nested Measures