Sum of Average with summarize

[thod]

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

    SUMX( Tabel, [MyMeasure] )

Br,
Thomas

[johnt75]

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

[thod]

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?

[johnt75]

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

[thod]

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?

[johnt75]

I can't think of anything else. Direct Query is always going to perform poorly when compared to imported data.

[thod]

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.

[johnt75]

You can't use SUMMARIZECOLUMNS in a measure. You can use it to build calculated tables, but that wouldn't react to slicers etc.

[thod]

Alright, thank you.