Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have three tables: [Category] that has relationship to [Purchase] that has relationship to [Customer]. All relationships are one to many.
Now I want to calculate number of customers that have bought only from specific category "B".
Doing a simple Calculate(DISTINCTCOUNT(Customer[Id], Filter[Category, Category[type] = "B") returns all sales from said category. I want to know number of customers who have only bought from this one category (once or more times).
Example
Customer | Purchase | Category |
1 | 100 | B |
1 | 101 | B |
2 | 102 | A |
2 | 103 | B |
3 | 104 | B |
4 | 105 | C |
This should return 2 because Customers 1 and 3 have bought only "B". Where as now it returns 3 because client 2 have also bought "B" as well as "A".
Solved! Go to Solution.
Hi, @Z7_852
You can try the following methods.
Measure:
Count1 = CALCULATE(DISTINCTCOUNT(Purchase[Category]),ALLEXCEPT(Customer,Customer[Customer]))
Count result =
Var _table=CALCULATETABLE(VALUES(Customer[Customer]),FILTER(ALL(Customer),[Count1]=1))
Return
CALCULATE(DISTINCTCOUNT(Customer[Customer]),FILTER(ALL(Category),[Category]="B"),FILTER(ALL(Customer),[Customer] in _table))
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Z7_852
You can try the following methods.
Measure:
Count1 = CALCULATE(DISTINCTCOUNT(Purchase[Category]),ALLEXCEPT(Customer,Customer[Customer]))
Count result =
Var _table=CALCULATETABLE(VALUES(Customer[Customer]),FILTER(ALL(Customer),[Count1]=1))
Return
CALCULATE(DISTINCTCOUNT(Customer[Customer]),FILTER(ALL(Category),[Category]="B"),FILTER(ALL(Customer),[Customer] in _table))
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI, @Z7_852
try below
Measure =
CALCULATE(DISTINCTCOUNT(Customer[Id]),
FILTER(
ADDCOLUMNS(
ALL('Customer[Id]),
"countofcategory",CALCULATE(DISTINCTCOUNT(Category[type]))
),
[d]<2
),
Category[type] = "B"
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
87 | |
71 | |
63 | |
60 |