Question regarding context (Time Intelligence Functions)

Power BI
1

Hi there!

I’m currently watching the modules about Time Intelligence Functions, but there is one thing that I cannot explain for myself. It is regarding the video: " Compare Current Sales To Previous Best Month ".

In that video we use the following DAX formula:

Highest Previous Sales Mth =
CALCULATE( [Total Sales];
TOPN( 1;
FILTER( SUMMARIZE( ALL( Dates ); Dates[Month & Year]; Dates[MonthnYear]);
Dates[MonthnYear] <= MIN( Dates[MonthnYear] ) );
[Total Sales]; DESC ) )

In this formula we remove all the context from the Dates table. However, if I use the slicer for the dates, the “Highest Previous Sales Month” only looks at months that are within that slicer.

Let me explain. On Jan 2015 we got Total Sales of 1.024.700. So if you set the slicer for Date 1-1-2015 till whatever, you get Highest Previous Sales Mnth is 1.024.700 until July 2015.

However, if we slice the Date table from 1-1-2015 to 1-3-2015 (so March), the Highest Previous Sales Mnth is 614.899.

I simply don’t get it why this formula still listens to the context as provided by the slicer as ALL should remove everything right?

Hope someone can help me :slight_smile:

David

2 Likes
2

Hi David,

FILTER sets the context for the CALCULATE. The slicer sets the context for the page/visuals. In other words, the slicer determines what date data is given to the formula to calculate.

Depending on your goal for the report, you’ve got a few different options on how to deal with this.

I hope this helps,

mickeydjw

3

Hi Mickey,

Thanks for your reply! Unfortunately I still don’t get it…

SUMMARIZE( ALL( Dates ); Dates[Month & Year]; Dates[MonthnYear])

This part of the formula would return a table with ALL the dates, ignoring the context as coming from the date slicer.

FILTER( SUMMARIZE( ALL( Dates ); Dates[Month & Year]; Dates[MonthnYear]);
Dates[MonthnYear] <= MIN( Dates[MonthnYear] ) )

Next up, the filter would cut out all the months that come AFTER the context coming from the row labels of the matrix.

TOPN( 1; 
    FILTER( SUMMARIZE( ALL( Dates ); Dates[Month & Year]; Dates[MonthnYear]);
        Dates[MonthnYear] <= MIN( Dates[MonthnYear] ) ); [Total Sales]; DESC )

Next, the TOPN function will find the month with the highest sales. So it looks at all the dates, also the dates that fall outside the date range from the slicer.

CALCULATE( [Total Sales]; TOPN function)

At the end, calculate will calculate the total sales for the month that had the highest sales.

Why does the calculate look at only dates within the date range from the slicer as opposed to the date that is coming from the TOPN function?

I hope I’m making my issue clear :slight_smile:

4

Hi David,

The context for Calculate is set by slicer, before the context is set for the TOPN function. Human example:

I order a large pizza. I know I’m going to eat 4 pieces. That will leave 4 pieces for my 3 kids. If they were to evenly split their pizza, they each get 1/3 of a half-pizza, even though that is ALL the kids pizza. When they ask for dessert, they’re going to claim that they ate ALL their pizza, because the context of their pizza didn’t include the pizza that I ate.

What happens if you modify the CALCULATE by adding another ALL (dates); before the TOPN function. Does it then ignore the slicer?

mickeydjw

1 Like
5

Hi Mickey,

Thanks for your help, but unfortunately I still don’t get it… I tried adding another ALL(Dates) as a filter expression within the CALCULATE function, but it gives the wrong results…

The way you describe ALL is the way I would describe ALLSELECTED…

image

Perhaps we can start within the center of the formula? The part that I have selected, does this part include dates that fall outside the slicer or not…? I have never heard before that ALL does only show dates that fall within a date slicer…

If you do for example TOTAL SALES ALL TIME = CALCULATE( [Total Sales] ; ALL(Dates)), the ALL(Dates) part will make sure that you look at all the dates in your dataset, whereas ALLSELECTED(Date) would only pick dates that belong to the time frame within the slicer.

6

Hi Dave,

Does this article help?

7

Hi, David! The slicer affects all visualization by default, but you can change it with “Edit interactions”. As you saw in a video the calculation ignores Month&Year in a table. I hope it helps:)

8

Always break down context into two parts.

Initial context, then within formula context

I go into this in detail in the below section in the Mastering DAX course.

The initial context coming from anything on your page, will basically hold within the formula/measure, unless you change it. Which can be done within CALCULATE or CALCULATETABLE.

In the SUMMARIZE example you have highlighted, due to this being inside CALCULATE, then yes it is changing the context of the calculation.

This best way to think of the SUMMARIZE though is as a virtual table.

Check out the section of videos below for a lot more ideas and information on this.

More example here as well

Thanks
Sam

1 Like
9

Hi @Dieffie, we’ve noticed that no response has been received from you since January 25, 2020. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!