Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
thod
Helper I
Helper I

Sum of Average with summarize

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 = 

VAR MaksPoliceDato = [Seneste dato i fact PoliceBestand]
VAR Tabel =
         SUMMARIZE(
        FILTER('PoliceBestand','PoliceBestand'[DW_SK_Dato] = MaksPoliceDato),
        'PoliceBestand'[DW_EK_Alder],
        'PoliceBestand'[DW_SK_Dato],
        'PoliceBestand'[DW_EK_Kontakt],
        "MyMeasure",
            AVERAGEX(
                FILTER(
            PoliceBestand,
            'PoliceBestand'[DW_EK_Alder] < 150
                && 'PoliceBestand'[DW_EK_Alder] <> -1
                && NOT ( ISBLANK'PoliceBestand'[DW_EK_Alder] ) )
                && 'PoliceBestand'[DW_SK_Dato] = MaksPoliceDato
        ),
                'PoliceBestand'[DW_EK_Alder]
                    *  DISTINCTCOUNT'PoliceBestand'[DW_EK_Kontakt] )
            )
    )
RETURN
    SUMXTabel[MyMeasure] )

Br,
Thomas
8 REPLIES 8
johnt75
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.