Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Filter context and variables (can someone explain different results?)

Using the following DAX I am trying to calculate the change in average monthly total sales over time at region level:

MA REGION =

VAR _mindate = MIN('Date'[Date])

VAR _table = CALCULATETABLE(ADDCOLUMNS(SUMMARIZE(Sheet1, 'Date'[Month Year], Sheet1[REGION]), "Sum Sales", CALCULATE(SUM(Sheet1[SALES]), REMOVEFILTERS(Sheet1[CITY]))), 'Date'[Date] <= _mindate)

RETURN

AVERAGEX(_table, [Sum Sales])

This gives me (what I think) are correct results:


What I don't understand is why the following do not give me the same results:

MA REGION 2 =

VAR _mindate = MIN('Date'[Date])

VAR _table = ADDCOLUMNS(SUMMARIZE(Sheet1, 'Date'[Month Year], Sheet1[REGION]), "Sum Sales", CALCULATE(SUM(Sheet1[SALES]), REMOVEFILTERS(Sheet1[CITY])))

RETURN

AVERAGEX(CALCULATETABLE(_table, 'Date'[Date] <= _mindate), [Sum Sales])


MA region 3 =

VAR _mindate = MIN('Date'[Date])

VAR _table = ADDCOLUMNS(SUMMARIZE(Sheet1, 'Date'[Month Year], Sheet1[REGION]), "Sum Sales", CALCULATE(SUM(Sheet1[SALES]), REMOVEFILTERS(Sheet1[CITY])))

RETURN

CALCULATE(AVERAGEX(_table, [Sum Sales]), 'Date'[Date] <= _mindate)


I presume it is to do with the way the filter context is handled but would appreciate help in understanding why the first measure gives different results to the others.

Thank you.

Need Help

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Variables in DAX aren't really variables, they're constants which are only evaluated once, at the point where they are declared.

    In your second 2 examples you are creating a static table and then applying a filter to 'Date'[Date], but the Date table cannot filter the variable because it isn't part of the model.

    If you wanted to use the technique in your third example you would need to include in the summary table a numeric representation of month year and you would then be able to use that as a filter.