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

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

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