Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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,
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:
Option 2:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |