How to filter, "Only" customers with products A, B and C and return in a table?

[Eder_JG]

Hey guys.

Please help me in the following case, in the table below, I need you to return to customers who had products A, B and C, the return needs to be "Only" the filtered values and a table.

I tried the formulas:

CALCULATE (
    COUNTA ( 'Sheet1' [Client] );
        KEEPFILTERS ( Sheet1 [Product] IN { "A" ; "B" ; "C" }

CALCULATE (
    COUNTA ( 'Sheet1' [Client] );
    'Sheet1' [Product] IN { "A" ; "B" ; "C" }

But they return all the values, I would like only the customers who have my "Conditions" to be displayed.

I tried that, but it returns nothing.

CALCULATE(
    CONTA('Sheet1'[Client]);
        'Sheet1'[Product] = "A" &&
           'Sheet1'[Product] = "B" &&
              'Sheet1'[Product] = "C"

[Nathaniel_C · Accepted solution]

Hi @Eder_JG 
If I understand your issue, correctly, please try this:

Clients who own all = 

Var _client = MAX(ClientProd[Client])

var _calc = CALCULATE(DISTINCTCOUNT(ClientProd[Product]), FILTER(ALL(ClientProd),ClientProd[Client]=_client))



return IF(_calc =3,3)

Table on the left is my original table. Table on the right are the owners and the Distinct Count.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @Eder_JG 
If I understand your issue, correctly, please try this:

Clients who own all = 

Var _client = MAX(ClientProd[Client])

var _calc = CALCULATE(DISTINCTCOUNT(ClientProd[Product]), FILTER(ALL(ClientProd),ClientProd[Client]=_client))



return IF(_calc =3,3)

Table on the left is my original table. Table on the right are the owners and the Distinct Count.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Eder_JG · Accepted solution]

Thanks a lot for your help, perfect.