Forum Discussion
Post Partisancalculate with summarize vs values
Hi,
I have created the below measure which is just a very expanded way of achviening the same result by using keepfilters but i am doing this for my own understading.
Contoso and Red only v3 = CALCULATE(
Sales[Sales Amount],
FILTER(
SUMMARIZE(
'Product',
'Product'[Brand],
'Product'[Color]
),
AND(
'Product'[Brand] = "Contoso",
'Product'[Color] = "Red"
)
)
)
This measure yields the correct results.
However, when I actually create the below table in my model and name it Summarise_Example
SUMMARIZE(
'Product',
'Product'[Brand],
'Product'[Color]
)
and join it up as follows:
and then replace it in my measure so it is like this:
Contoso and Red only v4 = CALCULATE(
Sales[Sales Amount],
Filter(Summarise_Example,
'Summarise_Example'[Brand] = "Contoso" &&
'Summarise_Example'[Color] = "Red"
)
)
the results are not the same (it is giving values when product=contoso and color <> red when i clearly specified it to be for contonso and red.
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
18 Replies
- Deku
Super User
You can see one of your relationship is dashed, which means it is inactive. You can only have a single relationship active between two tables.
You can transfer multiple values between tables in a measure using treatas.
Calculate(
Something,
Treatas( summarize( table3, table2[a], table2[b]),
Table1[a], table1[b]
)
If you want that new table to filter correctly you will need to a column to each table which is a common key for the relationship. This can. Just be the concatenation of the the two fields
mp390988Thank you for pointing me to the TREATAS function.
Given that the following is my model:
I implemented my measure as follows using the TREATAS function to create a virtual relationship between the two tables Summarise_Example and Product.Contoso and Red only v4 = CALCULATE( Sales[Sales Amount], Filter( TREATAS( SELECTCOLUMNS( Summarise_Example, "Brand", 'Summarise_Example'[Brand], "Color", 'Summarise_Example'[Color] ), 'Product'[Brand], 'Product'[Color] ), 'Product'[Brand] = "Contoso" && 'Product'[Color] = "Red" ) )But I am still seeing repeated values printed against brand and color that does not equal "Contoso" and "Red" as per the below diagram when I drag the above measure into the visual.why is this?- johnt75
You're using TREATAS to move the filters in the wrong direction. You need to place the filters on the summarized table and then move that filter using TREATAS to the Product table
Contoso and Red only v4 = CALCULATE ( Sales[Sales Amount], TREATAS ( FILTER ( Summarise_Example, 'Summarise_Example'[Brand] = "Contoso" && 'Summarise_Example'[Color] = "Red" ), 'Product'[Brand], 'Product'[Color] ) )
- Ashish_Mathur
Hi,
You should be able to simplify your measure to:
Contoso and Red only v3 = CALCULATE(SUM(Sales[Sales Amount]),'Product'[Brand] = "Contoso",'Product'[Color] = "Red") 
v-saisrao-msftCommunity Support
Hi mp390988,
I wanted to check if you had the opportunity to review the information provided by Ashish_Mathur, Deku. Please feel free to contact us if you have any further questions. If their response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.- mp390988
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
- v-saisrao-msft
Hi mp390988,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- v-saisrao-msft
Hi mp390988,
We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.
Thank you.
