Expression that compares difference of two sets based on filtering with aggregation

[NattiNatey]

I am using the following expression to filter for count of a dimension based on filtering with aggregation with an alternate state filter:

{[Group 1]} COUNT(
    IF((AGGR(SUM([Sales), [Product]) >= 50)

    AND (AGGR(SUM({<Category={'E'}>}[Sales]), [Product]) >= 20), [Product])
)

My goal is to use an alternate state to create an identical expression with another alternate state like below:

{[Group 2]} COUNT(
    IF((AGGR(SUM([Sales), [Product]) >= 50)

    AND (AGGR(SUM({<Category={'E'}>}[Sales]), [Product]) >= 20), [Product])
)

Then I want to compare the dimension values in each set to determine which values exist in Group 1 that do not exist in Group 2. The alternate state filters will be just a simple dimension = value (i.e. Year = 2025, Year = 2024)

The result of the expression will be the count of unique values only in Group 1. 

I tried below but had no success.

{<

{[Group 1]} COUNT(
    IF((AGGR(SUM([Sales), [Product]) >= 50)

    AND (AGGR(SUM({<Category={'E'}>}[Sales]), [Product]) >= 20), [Product])
)

)>
-
<

{[Group 2]} COUNT(
    IF((AGGR(SUM([Sales), [Product]) >= 50)

    AND (AGGR(SUM({<Category={'E'}>}[Sales]), [Product]) >= 20), [Product])
)

)>}

Any suggestions appreciated!

[Lisa_P]

Your first expression should be:

{Group 1}Count({<Product={"=Sum(Sales)>50"}>*<Category={E}>}Product)

and the second should be:

{Group 2}Count({<Product={"=Sum(Sales)>50"}>*<Category={E}>}Product)

so next would be ..

{Group 1}Count({<Product={"=Sum(Sales)>50"}>*<Category={E}>}Product)

-

{Group 2}Count({<Product={"=Sum(Sales)>50"}>*<Category={E}>}Product)

[NattiNatey]

Hi @Lisa_P , thanks so much for your response! I was unaware of this syntax where you can use the intersection of two filtered sets of data. I think there is one difference in your solution than in my question.

Your solution filters SUM(Sales) by Product > 50 in the first set and then Category = {'E'} in the second set. My question filters SUM(Sales) by Product > 50 (same as yours) in the first set and Category = {'E'} AND SUM(Sales) by Product > 20 in the second. 

Do you know if it is possible to add this second filter condition to the second set?

[Lisa_P]

Sorry, I must have copied and forgot to change, yes please change the second one.

[NattiNatey · Accepted solution]

I added the second filter and this worked 🙂

{Group 1}Count({<Product={"=Sum(Sales)>50"}>*<Product ={"={<Category={E}>}Sum(Sales)>20"}>}Product)

-

{Group 2}Count({<Product={"=Sum(Sales)>50"}>*<Product ={"={<Category={E}>}Sum(Sales)>20"}>}Product)