SSAS-Measures-Calculate function-filter context

Question

Hello,

A very simple question trying to understand the Calculate function.

For the below measure, If I dont specify the column field for the table in the filter context , then on what basis the sum gets calculated. If I dont specify the column name for the table 'product' it still gives me the same result as when used any of the field (Product[color],Product[name]) on the table? how does this logic works? please can someone explain it to me please?

ex:
calculate ( sum (sales[TotalAmount],Product)
calculate ( sum (sales[TotalAmount],Product[color])--same result as above
calculate ( sum (sales[TotalAmount],Product[name])--same result as above

Thanks in adv

Answer 1

You get the same result, because in query 2 + 3 you added only a dimension attribute, but not value to filter on, like = "Blue"

calculate ( sum (sales[TotalAmount],Product[color] = "Blue")  

See https://learn.microsoft.com/en-us/dax/calculate-function-dax => Examples.

Answer 2

Calculate is arguably the most complex function in DAX, despite its deceptively short syntax and straightforward name. The results of these expressions will vary, e.g., depending on the data model (e.g. using a physical table as a modifier is an ancient way of implementing M2M). You'll have to do quite a lot of reading to master it. https://www.sqlbi.com/articles/introducing-calculate-in-dax/ is currently a good starting point.

Answer 3

calculate ( sum (sales[TotalAmount]),Product) --> This is same like (SUM(sales[TotalAmount]) the table is

calculate ( sum (sales[TotalAmount]),Product[color]) incorrect statement--> calculate ( sum (sales[TotalAmount]),Product[color]="Blue") --->Once you write a column name that means Filter Context will be applied and you need to specify expression of your filter This will be translated like
[FILTER(All(Sales), Product[Color]="Blue"]

calculate ( sum (sales[TotalAmount]),Product[name]) --> incorrect statement--> calculate ( sum (sales[TotalAmount]),Product[name] ="Bikes") --> Will be translated to same as above

Answer 4

Hi @jennifer zen ,

calculate ( sum (sales[TotalAmount],Product)  

This means to get the sum of total amount for all the product.

calculate ( sum (sales[TotalAmount],Product[color])  

This means to get the sum of total amount for all the product color existed in product table.

calculate ( sum (sales[TotalAmount],Product[name])--same result as above   

This means to get the sum of total amount for all the product Name existed in product table.

So basically, they are equal, as we did not filter out any products in product table.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.