Example Transition Context in Dax

[Aquila]

Hello. I’m trying to understand the transition context of an article I’ve seen. These are two tables, one calendar and the other with sales.
The measures are as follows:

1-Total sales = SUM (sales [Amount]) – Total sales of the day

2-LD Sales Good =
CALCULATE ([Total sales];
FILTER (ALL (‘ Calendar ‘);
‘ Calendar ‘ [Day] = MAXX (‘ Calendar ‘; ‘ Calendar ‘ [Day])-1)))
Total sales of the previous day

3-LD Sales BadA =
CALCULATE ([Total Sales];
FILTER (ALL (‘ Calendar ‘);
‘ Calendar ‘ [Day] = CALCULATE(MAXX (‘ Calendar ‘; ‘ Calendar ‘ [Day])-1)))

I’ve added the Calculate function to study how it modifies the row context and transforms it into a filter context.

4-LD Sales BadA =
CALCULATE ([Total Sales];
FILTER (ALL (‘ Calendar ‘);
‘ Calendar ‘ [Day] = CALCULATE (MAXX (‘ Calendar ‘; ‘ Calendar ‘ [Day]))))

It’s similar to the previous measurement, only that I’ve removed (-1).

I developed a visual guide to understand the operation of these measures for the sole purpose of studying the same, but I can not understand how it works LD Sales BadA, and LD Sales BadB. I wish I could complete this guide with these examples.

 

[GraH - Guido]

Hi Aquila,

Care to share the source of this question? I'm very disturbed but the combinations of all those filter functions in the formula: calculate (nested even, that sure will mess with your head), filter, all. And then there is that MAXX as an iterator.
Rob Collie is perhaps the inspiration (or cause?): he calls his formula the greatest formula in the world or GFITW. Check out his blog (https://powerpivotpro.com/). Perhaps things will become clearer. In youtube you can also look for Alberto Ferrari. There are 2 seminars available on writing and understand how DAX works.

For what it is worth, I'll try to shed some light, though some finesses of DAX will remain a mystery for ever for me.
- All removes any initial filter context, and so thus calculate.
- Filter starts from the initial filter context and then applies more filters to extend it.
=> 4-LD Sales BadA: since MAXX iterates over the dates, calculate returns each day. Which indeed matches the calendar day. Then you filter these days by using all. So in fact you just CALCULATE Total Sales for All Days in the calendar. Hence the result.
=>3-LD Sales BadA: cannot work since there is no calendar date equal to the previous one, when you use an iteration. That is why it returns a blank result. Nothing ever matches these filter criteria.
=> 2-LD Sales Good works fine because you use the iterator in a raw filter (without using a new filter altering function, like calculate).

I hope this helped a little bit, but I kindly invite you to read/listen to the suggested material.

Cheers for now,
Guido

 

[Aquila]

Hi Aquila,

Care to share the source of this question? I'm very disturbed but the combinations of all those filter functions in the formula: calculate (nested even, that sure will mess with your head), filter, all. And then there is that MAXX as an iterator.
Rob Collie is perhaps the inspiration (or cause?): he calls his formula the greatest formula in the world or GFITW. Check out his blog (https://powerpivotpro.com/). Perhaps things will become clearer. In youtube you can also look for Alberto Ferrari. There are 2 seminars available on writing and understand how DAX works.

For what it is worth, I'll try to shed some light, though some finesses of DAX will remain a mystery for ever for me.
- All removes any initial filter context, and so thus calculate.
- Filter starts from the initial filter context and then applies more filters to extend it.
=> 4-LD Sales BadA: since MAXX iterates over the dates, calculate returns each day. Which indeed matches the calendar day. Then you filter these days by using all. So in fact you just CALCULATE Total Sales for All Days in the calendar. Hence the result.
=>3-LD Sales BadA: cannot work since there is no calendar date equal to the previous one, when you use an iteration. That is why it returns a blank result. Nothing ever matches these filter criteria.
=> 2-LD Sales Good works fine because you use the iterator in a raw filter (without using a new filter altering function, like calculate).

I hope this helped a little bit, but I kindly invite you to read/listen to the suggested material.

Cheers for now,
Guido

 

[Aquila]

Thank you very much. I leave the original link:
"

"

 

[Aquila]

Hi Aquila,

Care to share the source of this question? I'm very disturbed but the combinations of all those filter functions in the formula: calculate (nested even, that sure will mess with your head), filter, all. And then there is that MAXX as an iterator.
Rob Collie is perhaps the inspiration (or cause?): he calls his formula the greatest formula in the world or GFITW. Check out his blog (https://powerpivotpro.com/). Perhaps things will become clearer. In youtube you can also look for Alberto Ferrari. There are 2 seminars available on writing and understand how DAX works.

For what it is worth, I'll try to shed some light, though some finesses of DAX will remain a mystery for ever for me.
- All removes any initial filter context, and so thus calculate.
- Filter starts from the initial filter context and then applies more filters to extend it.
=> 4-LD Sales BadA: since MAXX iterates over the dates, calculate returns each day. Which indeed matches the calendar day. Then you filter these days by using all. So in fact you just CALCULATE Total Sales for All Days in the calendar. Hence the result.
=>3-LD Sales BadA: cannot work since there is no calendar date equal to the previous one, when you use an iteration. That is why it returns a blank result. Nothing ever matches these filter criteria.
=> 2-LD Sales Good works fine because you use the iterator in a raw filter (without using a new filter altering function, like calculate).

I hope this helped a little bit, but I kindly invite you to read/listen to the suggested material.

Cheers for now,
Guido

Thank you very much Guido. You really helped me a lot, and I made a guide showing visually how the transfer of contexts is done when using calculate. I’d like you to see her and give me your opinion. I think it might help others to understand the idea.

Attached two Excel books.

 

[GraH - Guido]

hi Aquila

There is a very good overview (and you can download it for free, but is (r), so I don't want to upload it here, since I think it is against the rules) on how context transition works on the site of Rob Collie (one of the original team members who invented Power Pivot). There are 2 versions: how the context travels trough the relationship of the data model, which is basically how the VertiPaq engine works; and the second one shows the impact of calculate/filter. Go to powerpivotpro dot com.


I like how you want to make a step by step visualisation on how it works: it is quit similar to what Alberto Ferrari does in his seminar. But I honestly doubt that your example is the best one to explain the principle. You might want to simplify it, so you explain how the relationship helps in making DAX measures. And then give an example what happens if you want to change the context. Before finally making the example of a badly designed DAX.

But very good job and nice initiative that you want to share this insight with your co-workers.

Cheers
G.

 

[Aquila]

hi Aquila

There is a very good overview (and you can download it for free, but is (r), so I don't want to upload it here, since I think it is against the rules) on how context transition works on the site of Rob Collie (one of the original team members who invented Power Pivot). There are 2 versions: how the context travels trough the relationship of the data model, which is basically how the VertiPaq engine works; and the second one shows the impact of calculate/filter. Go to powerpivotpro dot com.
View attachment 48602

I like how you want to make a step by step visualisation on how it works: it is quit similar to what Alberto Ferrari does in his seminar. But I honestly doubt that your example is the best one to explain the principle. You might want to simplify it, so you explain how the relationship helps in making DAX measures. And then give an example what happens if you want to change the context. Before finally making the example of a badly designed DAX.

But very good job and nice initiative that you want to share this insight with your co-workers.

Cheers
G.

Hello. Actually, I'm just doing an exercise raised by Brian Grant at "

", trying to use his own didactics, for personal learning, and to share with others. It has been hard for me to solve it, and it has taken several headaches. Thank you very much for your comments and contributions, have served me a lot.