Problem with dax query time

Question

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.

owenauger · Answer

MarcosVME&nbsp;- 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 🙂
&nbsp;
Your original measure applied the expanded table&nbsp;VEEVA_D_FORCA_DE_VENDAS&nbsp;(first filtered to nonblank values of VEEVA_D_FORCA_DE_VENDAS[COD_SETOR]&nbsp;) as a filter.
&nbsp;
I believe the original performance issue is due to applying FILTER (an iterator)&nbsp;to&nbsp;VEEVA_D_FORCA_DE_VENDAS.
&nbsp;
Here are a couple of options that I believe should improve performance and return the correct results (fingers crossed 🙂 )&nbsp;
&nbsp;
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] ) )
)
&nbsp;
&nbsp;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&nbsp;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?
&nbsp;
Regards

owenauger · Answer

Hi&nbsp;MarcosVME&nbsp;
Based on what you've posted, I would recommend turning&nbsp;vfilter into a column filter within the CALCULATE expression directly.
&nbsp;
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] ) )
)
&nbsp;
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.
&nbsp;
Does the above measure improve performance and still produce expected results?
&nbsp;
Regards

marcosvme · Answer

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 · Answer

Thanks for testing that out and sorry it didn't produce the same result.
&nbsp;
Could you post a picture of the data model diagram &amp; the definition of the measure&nbsp;[FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA]?
&nbsp;
I was making some standard assumptions about the data model but there could be something specific to consider with your model.

marcosvme · Answer

This is my data modeling below:And this is my full dax script:DEFINE&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_UNIDADES] = SUM(DEMANDA_F_CONCORRENTES[UNIDADE])&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_UNIDADES_MTH] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])RETURNCALCULATE(&nbsp; &nbsp; [DEMANDA_UNIDADES]&nbsp; &nbsp; ,DATESBETWEEN(&nbsp; &nbsp; &nbsp; &nbsp; D_CALENDARIO[DATE]&nbsp; &nbsp; &nbsp; &nbsp; ,STARTOFMONTH(LAST_DATE)&nbsp; &nbsp; &nbsp; &nbsp; ,ENDOFMONTH(LAST_DATE)&nbsp; &nbsp; ))&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_UNIDADES_TRI] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])RETURN&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE([DEMANDA_UNIDADES]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,DATESINPERIOD(D_CALENDARIO[DATE]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,LAST_DATE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,MONTH))&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_UNIDADES_YTD] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])RETURN&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [DEMANDA_UNIDADES]&nbsp; &nbsp; &nbsp; &nbsp; ,DATESBETWEEN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D_CALENDARIO[DATE]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,STARTOFYEAR(LAST_DATE)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,ENDOFMONTH(LAST_DATE)&nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_UNIDADES_MAT] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])RETURN&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE([DEMANDA_UNIDADES]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,DATESINPERIOD(D_CALENDARIO[DATE]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,LAST_DATE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,-12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,MONTH))&nbsp; &nbsp; MEASURE '_MEDIDAS'[FLEX_PERIODO_UNIDADES] = VAR PERIODO = SELECTEDVALUE(_METRICAS_PERIODO[ID_PERIODO])RETURNSWITCH(&nbsp; &nbsp; TRUE()&nbsp; &nbsp; ,PERIODO = 1,[DEMANDA_UNIDADES_MTH]&nbsp; &nbsp; ,PERIODO = 2,[DEMANDA_UNIDADES_TRI]&nbsp; &nbsp; ,PERIODO = 3,[DEMANDA_UNIDADES_YTD]&nbsp; &nbsp; ,PERIODO = 4,[DEMANDA_UNIDADES_MAT])&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF] = SUM(DEMANDA_F_CONCORRENTES[RS_PF])&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF_MTH] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])RETURNCALCULATE(&nbsp; &nbsp; [DEMANDA_REAIS_PF]&nbsp; &nbsp; ,DATESBETWEEN(&nbsp; &nbsp; &nbsp; &nbsp; D_CALENDARIO[DATE]&nbsp; &nbsp; &nbsp; &nbsp; ,STARTOFMONTH(LAST_DATE)&nbsp; &nbsp; &nbsp; &nbsp; ,ENDOFMONTH(LAST_DATE)&nbsp; &nbsp; ))&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF_TRI] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])RETURN&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE([DEMANDA_REAIS_PF]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,DATESINPERIOD(D_CALENDARIO[DATE]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,LAST_DATE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,MONTH))&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF_YTD] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])RETURN&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [DEMANDA_REAIS_PF]&nbsp; &nbsp; &nbsp; &nbsp; ,DATESBETWEEN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D_CALENDARIO[DATE]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,STARTOFYEAR(LAST_DATE)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,ENDOFMONTH(LAST_DATE)&nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp; &nbsp; MEASURE '_MEDIDAS'[DEMANDA_REAIS_PF_MAT] = VAR LAST_DATE = LASTDATE(D_CALENDARIO[DATE])RETURN&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE([DEMANDA_REAIS_PF]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,DATESINPERIOD(D_CALENDARIO[DATE]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,LAST_DATE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,-12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,MONTH))&nbsp; &nbsp; MEASURE '_MEDIDAS'[FLEX_PERIODO_REAIS] = VAR PERIODO = SELECTEDVALUE(_METRICAS_PERIODO[ID_PERIODO])RETURNSWITCH(&nbsp; &nbsp; TRUE()&nbsp; &nbsp; ,PERIODO = 1,[DEMANDA_REAIS_PF_MTH]&nbsp; &nbsp; ,PERIODO = 2,[DEMANDA_REAIS_PF_TRI]&nbsp; &nbsp; ,PERIODO = 3,[DEMANDA_REAIS_PF_YTD]&nbsp; &nbsp; ,PERIODO = 4,[DEMANDA_REAIS_PF_MAT])&nbsp; &nbsp; MEASURE '_MEDIDAS'[FLEX_METRICA_CALCULO] = VAR CALCULO = SELECTEDVALUE(_METRICAS_CALCULOS[ID_METRICA])RETURNSWITCH(&nbsp; &nbsp; TRUE()&nbsp; &nbsp; ,CALCULO = 1,[FLEX_PERIODO_UNIDADES]&nbsp; &nbsp; ,CALCULO = 2,[FLEX_PERIODO_REAIS])&nbsp; &nbsp; MEASURE '_MEDIDAS'[FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA] = CALCULATE(&nbsp; &nbsp; [FLEX_METRICA_CALCULO]&nbsp; &nbsp; ,NOT(&nbsp; &nbsp; &nbsp; &nbsp; ISBLANK(VEEVA_D_FORCA_DE_VENDAS[COD_REGIONAL])&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )&nbsp; &nbsp; MEASURE '_MEDIDAS'[FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA] = var vfilter = FILTER(&nbsp; &nbsp; VEEVA_D_FORCA_DE_VENDAS&nbsp; &nbsp; ,not ISBLANK(VEEVA_D_FORCA_DE_VENDAS[COD_SETOR]))var vContexto = CALCULATE(&nbsp; &nbsp; [FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA]&nbsp; &nbsp; ,USERELATIONSHIP(D_MARCA[MERCADO],IM_D_MERCADO_FRANQUIA[MERCADO])&nbsp; &nbsp; ,vfilter)RETURNvContextoEVALUATE&nbsp; &nbsp; SUMMARIZECOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_UNIDADES", '_MEDIDAS'[DEMANDA_UNIDADES],&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_UNIDADES_MTH", '_MEDIDAS'[DEMANDA_UNIDADES_MTH],&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_UNIDADES_TRI", '_MEDIDAS'[DEMANDA_UNIDADES_TRI],&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_UNIDADES_YTD", '_MEDIDAS'[DEMANDA_UNIDADES_YTD],&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_UNIDADES_MAT", '_MEDIDAS'[DEMANDA_UNIDADES_MAT],&nbsp; &nbsp; &nbsp; &nbsp; "FLEX_PERIODO_UNIDADES", '_MEDIDAS'[FLEX_PERIODO_UNIDADES],&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_REAIS_PF", '_MEDIDAS'[DEMANDA_REAIS_PF],&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_REAIS_PF_MTH", '_MEDIDAS'[DEMANDA_REAIS_PF_MTH],&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_REAIS_PF_TRI", '_MEDIDAS'[DEMANDA_REAIS_PF_TRI],&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_REAIS_PF_YTD", '_MEDIDAS'[DEMANDA_REAIS_PF_YTD],&nbsp; &nbsp; &nbsp; &nbsp; "DEMANDA_REAIS_PF_MAT", '_MEDIDAS'[DEMANDA_REAIS_PF_MAT],&nbsp; &nbsp; &nbsp; &nbsp; "FLEX_PERIODO_REAIS", '_MEDIDAS'[FLEX_PERIODO_REAIS],&nbsp; &nbsp; &nbsp; &nbsp; "FLEX_METRICA_CALCULO", '_MEDIDAS'[FLEX_METRICA_CALCULO],&nbsp; &nbsp; &nbsp; &nbsp; "FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA", '_MEDIDAS'[FLEX_METRICA_CALCULO_MERCADO_AREA_VISITADA],&nbsp; &nbsp; &nbsp; &nbsp; "FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA", '_MEDIDAS'[FLEX_METRICA_CALCULO_MERCADOMARCA_AREA_VISITADA]&nbsp; &nbsp; )

Forum Discussion

Problem with dax query time

7 Replies

Related content

Power Query Underlying Problem

how to resolve query timeout problem

Logic problem?

RLS problems with Direct Query

Slider problems