cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Credential
Frequent Visitor

Performance semantic model measure vs custom measure

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

11 REPLIES 11
m3tr01d
Continued Contributor
Continued Contributor

@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?

AlexisOlson
Super User
Super User

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).

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])
  )

 

Credential_1-1638350491877.png

 

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])
  )

 

 Credential_0-1638350411812.png

 

'CUSTOM.Measure'[CM_Neukunde_DIV_SUM] is exact the same as 'FACT.Measure'[Neukunde_DIV_SUM]


@Credential wrote:

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.

I was suggesting that it likely does have to do with the query optimizer but this is, in fact, related to Power BI updates. When you run a query in DAX Studio, it connects to the Power BI tabular analysis server, either a .pbix you have open locally or a dataset in the service (it doesn't have its own DAX engine).

But we only use SSAS tabular 1500 on premise. What does this have to do with Power BI updates?
The measure is defined once in the tabular model itself and once in the query itself. Both queries are sent to SSAS tabular 1500 and evaluated there via the FE and SE, right?

What I do not understand?

OK. I may not have fully understood the specifics of your question. Let me make sure we're on the same page. Is the following accurate?

 

You are using Power BI with a live connection to an SSAS tabular model (not Import or DirectQuery). You have a measure that performs better as a Report Level Measure (as defined here) than the same measure defined within the SSAS model.

Yes, that's correct.

That is indeed quite strange. I can't think of a good reason for a Report Level Measure to be faster than the same measure defined in the tabular model. Can you verify that it's not related to caching?

I know and don't understand either.
We always execute the queries in DAX Studio with a cleared cache. I have no idea why the one time 17 queries and the other time 35 queries are executed on SE.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors