Problem with dax query time

[MarcosVME]

Hey guys, I am having processing problems in a dax query below:

FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA =

var vfilter =

FILTER(

    VEEVA_D_FORCA_DE_VENDAS

    ,not ISBLANK(VEEVA_D_FORCA_DE_VENDAS[COD_SETOR])

)

var vContexto =

CALCULATE(

    [FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA]

    ,USERELATIONSHIP(D_MARCA[MERCADO],IM_D_MERCADO_FRANQUIA[MERCADO])

    ,vfilter

)

RETURN

vContexto

I need to apply it to calculate my model correctly. When I don't apply the vfilter variable, the query runs immediately, but when I apply the variable, a memory overflow error appears.

Any suggestions on how I could rewrite this code?
Remembering that my data model contains more than 200 million rows in the fact table.

[MarcosVME]

It actually improved performance, but it doesn't produce the same expected result, I believe I have to maintain the same table filter context in a way that improves performance.

but thanks for the suggestion

[OwenAuger]

Thanks for testing that out and sorry it didn't produce the same result.

Could you post a picture of the data model diagram & the definition of the measure [FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA]?

I was making some standard assumptions about the data model but there could be something specific to consider with your model.

[MarcosVME]

This is my data modeling below:

And this is my full dax script:

DEFINE

    MEASURE '_MEDIDAS'[DEMANDA_UNIDADES] = SUM(DEMANDA_F_CONCORRENTES[UNIDADE])

    MEASURE '_MEDIDAS'[DEMANDA_UNIDADES_MTH] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])

RETURN

CALCULATE(

    [DEMANDA_UNIDADES]

    ,DATESBETWEEN(

        D_CALENDARIO[DATE]

        ,STARTOFMONTH(LAST_DATE)

        ,ENDOFMONTH(LAST_DATE)

    )

)

    MEASURE '_MEDIDAS'[DEMANDA_UNIDADES_TRI] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])

RETURN

        CALCULATE([DEMANDA_UNIDADES]

            ,DATESINPERIOD(D_CALENDARIO[DATE]

                ,LAST_DATE

                  ,-3

                  ,MONTH))

    MEASURE '_MEDIDAS'[DEMANDA_UNIDADES_YTD] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])

RETURN

    CALCULATE(

        [DEMANDA_UNIDADES]

        ,DATESBETWEEN(

            D_CALENDARIO[DATE]

            ,STARTOFYEAR(LAST_DATE)

            ,ENDOFMONTH(LAST_DATE)

        )

)

    MEASURE '_MEDIDAS'[DEMANDA_UNIDADES_MAT] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])

RETURN

        CALCULATE([DEMANDA_UNIDADES]

            ,DATESINPERIOD(D_CALENDARIO[DATE]

                ,LAST_DATE

                  ,-12

                  ,MONTH))

    MEASURE '_MEDIDAS'[FLEX_PERIODO_UNIDADES] = VAR PERIODO = SELECTEDVALUE(_METRICAS_PERIODO[ID_PERIODO])

RETURN

SWITCH(

    TRUE()

    ,PERIODO = 1,[DEMANDA_UNIDADES_MTH]

    ,PERIODO = 2,[DEMANDA_UNIDADES_TRI]

    ,PERIODO = 3,[DEMANDA_UNIDADES_YTD]

    ,PERIODO = 4,[DEMANDA_UNIDADES_MAT]

)

    MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF] = SUM(DEMANDA_F_CONCORRENTES[RS_PF])

    MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF_MTH] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])

RETURN

CALCULATE(

    [DEMANDA_REAIS_PF]

    ,DATESBETWEEN(

        D_CALENDARIO[DATE]

        ,STARTOFMONTH(LAST_DATE)

        ,ENDOFMONTH(LAST_DATE)

    )

)

    MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF_TRI] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])

RETURN

        CALCULATE([DEMANDA_REAIS_PF]

            ,DATESINPERIOD(D_CALENDARIO[DATE]

                ,LAST_DATE

                  ,-3

                  ,MONTH))

    MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF_YTD] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])

RETURN

    CALCULATE(

        [DEMANDA_REAIS_PF]

        ,DATESBETWEEN(

            D_CALENDARIO[DATE]

            ,STARTOFYEAR(LAST_DATE)

            ,ENDOFMONTH(LAST_DATE)

        )

)

    MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF_MAT] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])

RETURN

        CALCULATE([DEMANDA_REAIS_PF]

            ,DATESINPERIOD(D_CALENDARIO[DATE]

                ,LAST_DATE

                  ,-12

                  ,MONTH))

    MEASURE '_MEDIDAS'[FLEX_PERIODO_REAIS] = VAR PERIODO = SELECTEDVALUE(_METRICAS_PERIODO[ID_PERIODO])

RETURN

SWITCH(

    TRUE()

    ,PERIODO = 1,[DEMANDA_REAIS_PF_MTH]

    ,PERIODO = 2,[DEMANDA_REAIS_PF_TRI]

    ,PERIODO = 3,[DEMANDA_REAIS_PF_YTD]

    ,PERIODO = 4,[DEMANDA_REAIS_PF_MAT]

)

    MEASURE '_MEDIDAS'[FLEX_METRICA_CALCULO] = VAR CALCULO = SELECTEDVALUE(_METRICAS_CALCULOS[ID_METRICA])

RETURN

SWITCH(

    TRUE()

    ,CALCULO = 1,[FLEX_PERIODO_UNIDADES]

    ,CALCULO = 2,[FLEX_PERIODO_REAIS]

)

    MEASURE '_MEDIDAS'[FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA] = CALCULATE(

    [FLEX_METRICA_CALCULO]

    ,NOT(

        ISBLANK(VEEVA_D_FORCA_DE_VENDAS[COD_REGIONAL])

        )

    )

    MEASURE '_MEDIDAS'[FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA] = var vfilter =

FILTER(

    VEEVA_D_FORCA_DE_VENDAS

    ,not ISBLANK(VEEVA_D_FORCA_DE_VENDAS[COD_SETOR])

)

var vContexto =

CALCULATE(

    [FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA]

    ,USERELATIONSHIP(D_MARCA[MERCADO],IM_D_MERCADO_FRANQUIA[MERCADO])

    ,vfilter

)

RETURN

vContexto

EVALUATE

    SUMMARIZECOLUMNS(

        "DEMANDA_UNIDADES", '_MEDIDAS'[DEMANDA_UNIDADES],

        "DEMANDA_UNIDADES_MTH", '_MEDIDAS'[DEMANDA_UNIDADES_MTH],

        "DEMANDA_UNIDADES_TRI", '_MEDIDAS'[DEMANDA_UNIDADES_TRI],

        "DEMANDA_UNIDADES_YTD", '_MEDIDAS'[DEMANDA_UNIDADES_YTD],

        "DEMANDA_UNIDADES_MAT", '_MEDIDAS'[DEMANDA_UNIDADES_MAT],

        "FLEX_PERIODO_UNIDADES", '_MEDIDAS'[FLEX_PERIODO_UNIDADES],

        "DEMANDA_REAIS_PF", '_MEDIDAS'[DEMANDA_REAIS_PF],

        "DEMANDA_REAIS_PF_MTH", '_MEDIDAS'[DEMANDA_REAIS_PF_MTH],

        "DEMANDA_REAIS_PF_TRI", '_MEDIDAS'[DEMANDA_REAIS_PF_TRI],

        "DEMANDA_REAIS_PF_YTD", '_MEDIDAS'[DEMANDA_REAIS_PF_YTD],

        "DEMANDA_REAIS_PF_MAT", '_MEDIDAS'[DEMANDA_REAIS_PF_MAT],

        "FLEX_PERIODO_REAIS", '_MEDIDAS'[FLEX_PERIODO_REAIS],

        "FLEX_METRICA_CALCULO", '_MEDIDAS'[FLEX_METRICA_CALCULO],

        "FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA", '_MEDIDAS'[FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA],

        "FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA", '_MEDIDAS'[FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA]

    )

[OwenAuger]

@MarcosVME - thanks for the extra detail on the model!

The relationships add a bit of complexity, but I think explain why my suggested measure wasn't working 🙂

Your original measure applied the expanded table VEEVA_D_FORCA_DE_VENDAS (first filtered to nonblank values of VEEVA_D_FORCA_DE_VENDAS[COD_SETOR] ) as a filter.

I believe the original performance issue is due to applying FILTER (an iterator) to VEEVA_D_FORCA_DE_VENDAS.

Here are a couple of options that I believe should improve performance and return the correct results (fingers crossed 🙂 ) 

Option 1:

Applies the nonblank filter in outer CALCULATE, then applies the expanded VEEVA_D_FORCA_DE_VENDAS table filter.

FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA =
CALCULATE (
    CALCULATE (
        [FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA],
        USERELATIONSHIP ( D_MARCA[MERCADO], IM_D_MERCADO_FRANQUIA[MERCADO] ),
        VEEVA_D_FORCA_DE_VENDAS
    ),
    KEEPFILTERS ( NOT ISBLANK ( VEEVA_D_FORCA_DE_VENDAS[COD_SETOR] ) )
)

 Option 2:

Applies the nonblank filter in outer CALCULATE, and in inner calculate uses SUMMARIZE to apply a filter on only column on 1-side of relationship with VEEVA_D_FORCA_DE_VENDAS (LINHA).

FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA =
CALCULATE (
    CALCULATE (
        [FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA],
        USERELATIONSHIP ( D_MARCA[MERCADO], IM_D_MERCADO_FRANQUIA[MERCADO] ),
        SUMMARIZE ( VEEVA_D_FORCA_DE_VENDAS, D_LINHA[LINHA] )
    ),
    KEEPFILTERS ( NOT ISBLANK ( VEEVA_D_FORCA_DE_VENDAS[COD_SETOR] ) )
)

Do either of these produce the correct results with better performance?

Regards

[MarcosVME]

Thanks for your great suggestions.

Option 1 worked and returned the same value, I even tried using crossjoin and it also worked, but it seems that the performance is still bad... in the model the DEMANDA_SETOR_BRICK table has 3 million rows and I believe that it is directly affecting performance due to the relations...

CALCULATE(

    CALCULATE(

        [FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA]

        ,USERELATIONSHIP(D_MARCA[MERCADO],IM_D_MERCADO_FRANQUIA[MERCADO])

    )

    ,CROSSJOIN(VEEVA_D_FORCA_DE_VENDAS, D_LINHA)

)

[OwenAuger]

Understood - thanks for the update.
I'm not sure I can help much more here - this probably requires more detailed inspection of the model and testing to resolve.

[OwenAuger]

Hi @MarcosVME 

Based on what you've posted, I would recommend turning vfilter into a column filter within the CALCULATE expression directly.

Something like this:

FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA =
CALCULATE (
    [FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA],
    USERELATIONSHIP ( D_MARCA[MERCADO], IM_D_MERCADO_FRANQUIA[MERCADO] ),
    KEEPFILTERS ( NOT ISBLANK ( VEEVA_D_FORCA_DE_VENDAS[COD_SETOR] ) )
)

Generally performance is better with filters on columns rather than tables, and use KEEPFILTERS if the filter needs to intersect with the current filter context.

Does the above measure improve performance and still produce expected results?

Regards