cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stanhodges Frequent Visitor
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
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 (
        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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Super User
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




stanhodges Frequent Visitor
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
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 (
        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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Community Support Team
Community Support Team

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

Hi @stanhodges 

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

1.png

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.