Forum Discussion
Post Partisancalculate with summarize vs values
Hi,
Yes I have had to opportunity to look at the information provided by Deku and Ashish_Mathur .
I find Deku suggestion helpful and I have taken on their suggestion and responded back on this thread, now I am awaiting for a repsonse to help me further.Thank You
Super UserIf you want to the filters for red and contoso to also respect the filters from your matrix then you need to use KEEPFILTERS.
Contoso and Red only v4 =
CALCULATE (
Sales[Sales Amount],
KEEPFILTERS (
TREATAS (
FILTER (
Summarise_Example,
'Summarise_Example'[Brand] = "Contoso"
&& 'Summarise_Example'[Color] = "Red"
),
'Product'[Brand],
'Product'[Color]
)
)
)
Without the KEEPFILTERS then the filters applied in the measure will overwrite the filters coming from the matrix, which is why you see the same value everywhere.
Post PartisanHmm, I not sure why you would need to wrap keepfilters because the below piece of code works without the KEEPFILTERS:
Contoso and Red only v3 = CALCULATE(
Sales[Sales Amount],
FILTER(
SUMMARIZE(
'Product',
'Product'[Brand],
'Product'[Color]
),
AND(
'Product'[Brand] = "Contoso",
'Product'[Color] = "Red"
)
)
)
All I done is modified the above piece of code by actually creating a table that is defined as follows:
Summarise_Example = SUMMARIZE(
'Product',
'Product'[Brand],
'Product'[Color]
)
and substituted this created table in the original piece of code to make the following:
Contoso and Red only v4 = CALCULATE(
Sales[Sales Amount],
Filter(
TREATAS(
Summarise_Example,
'Product'[Brand],
'Product'[Color]
),
'Product'[Brand] = "Contoso" &&
'Product'[Color] = "Red"
)
)
So I have no idea why this code is any different to the first code?
Basically:
Contoso and Red only v3 = CALCULATE(
Sales[Sales Amount],
FILTER(
SUMMARIZE(
'Product',
'Product'[Brand],
'Product'[Color]
),
AND(
'Product'[Brand] = "Contoso",
'Product'[Color] = "Red"
)
)
)
VS
Contoso and Red only v4 = CALCULATE(
Sales[Sales Amount],
Filter(
TREATAS(
Summarise_Example,
'Product'[Brand],
'Product'[Color]
),
'Product'[Brand] = "Contoso" &&
'Product'[Color] = "Red"
)
)- johnt75
In your v3 version, the SUMMARIZE is executed in a context where there is already a filter on brand and colour. When you apply the filter it will return a blank table except for the specific case where brand = contoso and colour = red. That's why you don't see results for those rows.
When you are using the created table, the filter in the matrix on product brand and colour has no effect on the created table, so for every combination the filter will return contoso and red. That is why you see a result for every row.
- mp390988
hey johnt75 - thank you for your explanation. When you say :
the SUMMARIZE is executed in a context where there is already a filter on brand and colourare you essentially saying the table created using SUMMARIZE is created on the fly each time you move to the next row in the visual and therefore, it gets the filter context coming from the visual and whenever you are in a row in the visual where product colour does not equal red or product is not contonso, the Summarize table is empty because of the following part marked in yellow:
Thank You
- johnt75
Yes, exactly.