Forum Discussion

mp390988's avatar
mp390988
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

calculate 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...
Need Help
  • mp390988's avatar
    mp390988
    1 year ago

    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

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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's avatar
mp390988
Icon for Post Partisan rankPost Partisan
1 year ago

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's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      mp390988
      Icon for Post Partisan rankPost Partisan
      1 year ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        1 year ago

        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.