What is CALCULATE doing in this simple measure?

[mkeisha]

I created the following measure that actually does what I intended it to do but I don't understand what "calculate" is doing. Here's the measure:

Total Qty of Line Items = SUMX(VALUES(SALES[OrderNumber]), CALCULATE([Line MAX]))

It is correctly providing the total number of line items. The VALUES(SALES[OrderNumber] creates a virtual table of unique order numbers (because my sales table had duplicate order numbers) and then Line MAX refers to the number of line items within each order. But why do we need the CALCULATE in the measure before the SUMX goes ahead and adds up all the line items? I think it has to do with context transition but isn't this measure already in a row context? 

Thank you.

[mkeisha]

Hi thanks for your reply.  Here are all the measures:Here's the  Sales1 table (just a few rows with sample data):

And here's the pivot table output with the [Total Lines] measure using CALCULATE correctly.

 Appreciate your help.

[AkhilAshok]

In your Line Total measure you don't need a CALCULATE, since you are already using a measure [Line Max]. All measures has an implicit calculate wraped around it.

But, it is not a good practice to do Context Transition over a high granular column like Order Number. It tend to take up lot of memory as the number of rows in your Datamodel increase, and can slow down the report. Why can't you just use:

Total Lines = COUNTROWS(SALES1)

If the granularity of Table is going further below Line, then it may not work. In addition, in your example dataset, if user filter for Order Date: 9/29/2003, you will get Total Lines as 2. But it should be 1 though right? If the requirement is to see 2 in such case, then your approach maybe correct.

[TeigeGao · Accepted solution]

Hi @mkeisha ,

Based on my test using your sample data, it will return the same result whether using Calculate() function.

Actually, they should return the same result.

Best Regards,

Teige

[TeigeGao]

Hi @mkeisha ,

Based on my test, the CALCULATE() function in the SUMX() function will not affect the result because SUMX() function has created the context. Generally, we will use CALCULATE() function to change the context.

If in your scenario, only CALCULATE() can work, could you please share some sample data and the measure [Line MAX] to help us analysis this problem.

Best Regards,

Teige

[ChrisMendoza]

@mkeisha -

I am definitely not qualified to explain this however, this may be useful to you https://www.sqlbi.com/articles/understanding-context-transition/.