cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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.

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors