CALCULATE function behaviour

[Ranjith_96]

Hi Power BI Community,

This is my DAX

ChairsSales = CALCULATE(SUM(Orders[Sales]),Orders[Sub-Category]="Chairs")

 Please refer the below image, I have written the Sub Category = Chairs in the CALULATE function, so ideally i am expecting the Sales value only where Sub-Category = Chairs, but why I am getting Sales Value where category is Furniture?  

I wanted to know, how my DAX will execute here,  

ChairsSales = CALCULATE(SUM(Orders[Sales]),Orders[Sub-Category]="Chairs")

How come its showing Sales Value of Chairs for all the Furnitues? Please explain me the execution of the DAX to me.

Please find the pbix file in the below URL for your reference.

https://1drv.ms/u/s!AvQPa0SWoli2iCaMqmBED2-E9yDG?e=ZKHZic

Thanks in Advance.

[parry2k]

@Ranjith_96 BTW you didn't watch the 15min video in 2 minutes otherwise it would have cleared the doubt. WE all want quick answers without putting effort, which is ok but sometimes you need to go the extra mile to understand the concepts. cheers!

[parry2k]

@Ranjith_96 correct

[parry2k · Accepted solution]

@Ranjith_96 if you understood it correctly, tell me what is the expected output of the following measure if you put it in the matrix visual. Try to answer it without actually testing it in your visual, If you are able to answer, it means you understood the concept.

ChairsSales = CALCULATE(SUM(Orders[Sales]),Orders[Category]="Furniture")

[Ranjith_96]

Hi @parry2k , I guess this DAX will give the sales values of respective subcategory wherever we have category as furniture, am i crct?

ChairsSales = CALCULATE(SUM(Orders[Sales]),Orders[Category]="Furniture")

[Ranjith_96]

@parry2k ..so can u confirm is this how the calulate will execute for my DAX,

ChairsSales = CALCULATE(SUM(Orders[Sales]),Orders[Sub-Category]="Chairs")

refer the below image,

in the first row, Category = Furniture and SubCategory = Chairs so 328449

in the seond row, Category = Furniture and SubCategory = Chairs so 328449

in the third row, Category = Furniture, and SubCategory = Chairs so 328449

in the fourth row, Category = Furniture and SubCategory = Chairs so 328449

in the fifth row, Category = Office Supplies and Sub Cateogry = Chairs , so no matching so NULL 

[parry2k]

@Ranjith_96 you need to understand how row context played role in the measures.

[parry2k]

@Ranjith_96 Basically Furniture on rows is getting applied to the measure, and this Orders[Sub-Category]="Chairs" condition means, remove filters on Sub-Category.

[parry2k]

@Ranjith_96 use KEEPFILTERS and check video on my YT channel on how it works: (1) Let's have a closer look at VALUES, DISTINCT and KEEPFILTERS DAX functions - Power BI - YouTube

ChairsSales = CALCULATE(SUM(Orders[Sales]),KEEPFILTERS(Orders[Sub-Category]="Chairs"))

[Ranjith_96]

Hi @parry2k ..Thanks much for your response,

However my concern is I wanted to know how the DAX function is operating in my case to give the values of chairs for all the categories which is = Furniture.

I knew, Using keepfilters, we can overcome with this,

My concern is how I am getting values of chairs for all the categories which is = Furniture. I wanted to understand how DAXs operating here to give that output. Could you pleas explain me on this. I watched the video, but couldnt help my case to understand how it executes.