calculate with summarize vs values

[mp390988]

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.

 

[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.

[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,
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 · Accepted solution]

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

[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")

[Deku]

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

[mp390988]

Thank 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?

[Deku]

Contoso and Red only v4 =

CALCULATE(

[Sales Amount],
KEEPFILTER(

TREATAS(
{"Contoso", "Red"},
Summarise_Example[Brand],
Summarise_Example[Color]
)

)

)

[mp390988]

That gives me an error:

[Deku]

Think it wants

{{"Contoso"}, {"Red"}},

[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]
    )
)

[mp390988]

Yes, you are right, I was using the TREATAS incorrectly. Before I read your response, I edited my answer to correct to the below:

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 even this as well as your answer, still show repeating values for brand and color that does not equal "Contoso" and "Red".

[johnt75]

If 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.

[mp390988]

Hmm, 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 colour

are 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

[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.

[johnt75]

Yes, exactly.