Measure DistinctCount of a row based on multiple filters of another row

[Anonymous]

I need to come up with a solution to the problem below.

I want to count the number of unique Users (User ID) who only buy Product A.  The answer the chart below would be 2 unique users. User ID 11 and User ID 13

ID	User ID	Product	Amount
1	10	A	10
2	10	B	5
3	11	A	10
4	12	C	20
5	13	A	10
6	12	A	10
7	11	A	10

[v-cherch-msft]

Hi @Anonymous 

You may try below measure:

UniqueCount =
VAR _table =
    SUMMARIZE ( Sales, Sales[User ID], "Count", DISTINCTCOUNT ( Sales[Product] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Sales[User ID] ),
        FILTER ( _table, [Count] = 1 && MAX ( Sales[Product] ) = "A" )
    )

Regards,

[MFelix]

Hi @Anonymous ,

You have two option to make this:

Assuming you are making this on a card visual (but works for other type of visuals

Option 1:

• Place the User on the card visual
• Select the summarization Count (distinct)
• Place the Product on the filter for the visual
• Select Product A on the filter

Option 2:

• Create a measure with the following code:

Users for product A = CALCULATE(DISTINCTCOUNT(Sales[Users]);Sales[Product] = "A")

Depending on the way you want to show the information both have advantages and disavantages.

Regards,

MFelix

[Anonymous]

Thanks @MFelix ,

I was not clear in my original answer I have updated the original question to better address my need. Your current solutions will count all Users with product "A". I only want the unique user count for users who purchased only Product "A" and nothing else. 

Thanks. 

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following measure:

Users for product A =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                ALL ( Sales[Product]; Sales[User ID] );
                Sales[Product];
                Sales[User ID]
            );
            "Count"; CALCULATE (
                DISTINCTCOUNT ( Sales[Product] );
                ALLEXCEPT ( Sales; Sales[User ID] )
            )
        );
        [Count] = 1
            && Sales[Product] = "A"
    )
)

Regards,

MFelix