question

jenniferzen-7686 avatar image
0 Votes"
jenniferzen-7686 asked AlexeiStoyanovsky answered

SSAS-Measures-Calculate function-filter context

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

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered jenniferzen-7686 commented

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://docs.microsoft.com/en-us/dax/calculate-function-dax => Examples.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your quick reply.

so its like basically saying 'calculate the sum of totalAmount for all the products' in all the three queries?

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @jenniferzen-7686 ,

 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

FarhanAhmed-6697 avatar image
0 Votes"
FarhanAhmed-6697 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.