Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I have a measure that returns the correct data, both on rows and on the totals in a matrix. However it performs really bad when slicing. Can someone please help identify how I can performance optimize my dax measure and obtain the same result?
Measure =
It's not best practice to use SUMMARIZE to add a calculated column, its better to use that just for grouping and to use ADDCOLUMNS to add the calculated columns. Try
TmpMeasure =
VAR MaksPoliceDato = [Seneste dato i fact PoliceBestand]
VAR Tabel =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( 'PoliceBestand', 'PoliceBestand'[DW_SK_Dato] = MaksPoliceDato ),
'PoliceBestand'[DW_EK_Alder],
'PoliceBestand'[DW_SK_Dato],
'PoliceBestand'[DW_EK_Kontakt]
),
"MyMeasure",
CALCULATE (
AVERAGEX (
PoliceBestand,
'PoliceBestand'[DW_EK_Alder] * DISTINCTCOUNT ( 'PoliceBestand'[DW_EK_Kontakt] )
),
'PoliceBestand'[DW_EK_Alder] < 150
&& 'PoliceBestand'[DW_EK_Alder] <> -1
&& NOT ( ISBLANK ( 'PoliceBestand'[DW_EK_Alder] ) )
&& 'PoliceBestand'[DW_SK_Dato] = MaksPoliceDato
)
)
RETURN
SUMX ( Tabel, [MyMeasure] )
Hi,
Thanks for the quick reply!
The ADDCOLUMNS improved the performance a bit, however the DAX query still spends 24 sec, when having YEAR and MONTH on rows in a table (for 1,5 years).
We are using Direct Query reading from an on prem SSAS.
Any suggestions on how to improve it even more?
There's another couple of tweaks you could make. Rather than filtering the entire table you could just filter the columns you need. You will need to check that this gives the same result as you are currently getting.
Also, in the CALCULATE, you can split the filters on DW_EK_Alder and DW_SK_Dato into separate conditions.
TmpMeasure =
VAR MaksPoliceDato = [Seneste dato i fact PoliceBestand]
VAR Tabel =
ADDCOLUMNS (
SUMMARIZE (
FILTER (
ALL (
'PoliceBestand'[DW_EK_Alder],
'PoliceBestand'[DW_SK_Dato],
'PoliceBestand'[DW_EK_Kontakt],
'PoliceBestand'[DW_SK_Dato]
),
'PoliceBestand'[DW_SK_Dato] = MaksPoliceDato
),
'PoliceBestand'[DW_EK_Alder],
'PoliceBestand'[DW_SK_Dato],
'PoliceBestand'[DW_EK_Kontakt]
),
"MyMeasure",
CALCULATE (
AVERAGEX (
PoliceBestand,
'PoliceBestand'[DW_EK_Alder] * DISTINCTCOUNT ( 'PoliceBestand'[DW_EK_Kontakt] )
),
'PoliceBestand'[DW_EK_Alder] < 150
&& 'PoliceBestand'[DW_EK_Alder] <> -1
&& NOT ( ISBLANK ( 'PoliceBestand'[DW_EK_Alder] ) ),
'PoliceBestand'[DW_SK_Dato] = MaksPoliceDato
)
)
RETURN
SUMX ( Tabel, [MyMeasure] )
Thanks again.
The new DAX measure performed the same as the other one you gave me. Do you have other suggestions? Or should the whole data model be rethinked?
I can't think of anything else. Direct Query is always going to perform poorly when compared to imported data.
Do you know if there is a way to use SUMMARIZECOLUMNS instead of ADDCOLUMNS AND SUMMARIZE. In Dax Studio it seems to perform better, but I cannot get it to work in my visualizations.
I want a table grouped by year and months with the ability to use different slicers.
You can't use SUMMARIZECOLUMNS in a measure. You can use it to build calculated tables, but that wouldn't react to slicers etc.
Alright, thank you.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |