Forum Discussion
Performance semantic model measure vs custom measure
Super UserPower 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])
)
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]
- AlexisOlson
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?
- AlexisOlson
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.