cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Measure DistinctCount of a row based on multiple filters of another row

Hi @stanhodges ,

Try the following measure:

```Users for product A =
COUNTROWS (
FILTER (
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

Proud to be a Datanaut!

4 REPLIES 4
Super User

## Re: Measure DistinctCount of a row based on multiple filters of another row

Hi @stanhodges ,

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

Proud to be a Datanaut!

Frequent Visitor

## Re: Measure DistinctCount of a row based on multiple filters of another row

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.

Super User

## Re: Measure DistinctCount of a row based on multiple filters of another row

Hi @stanhodges ,

Try the following measure:

```Users for product A =
COUNTROWS (
FILTER (
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

Proud to be a Datanaut!

Community Support Team

## Re: Measure DistinctCount of a row based on multiple filters of another row

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.