Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
v-cherch-msft
Microsoft Employee
Microsoft Employee

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

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.
MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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. 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.