Skip to main content
cancel
Showing results for 
Search instead 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

Reply
brownrice
Helper III
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:

brownrice_0-1686335406191.png


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

brownrice_1-1686335600642.png

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)

brownrice_2-1686335652162.png


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.

1 REPLY 1
johnt75
Super User
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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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