Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
i want in this table to count the customers that belong only to category a but not in other
result should be 3 (customer 2,3,5)
customer | category |
1 | a |
1 | b |
2 | a |
3 | a |
4 | a |
4 | b |
5 | a |
i used this function
onlycategorya:=CALCULATE (DISTINCTCOUNT (table[customer] );FILTER(VALUES(table[customer]);AND (CALCULATE ( HASONEVALUE (table[customer]));FIRSTNONBLANK(table[customer];0)="a")))
and it works but it is really slow in large dataset (20million rows)
any other ideas about optimizing this?
Solved! Go to Solution.
Hi @giorgiokatr
Sorry I missed the "a" in your post
Revised MEasure
MEASURE = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[customer], "MyCount", DISTINCTCOUNT ( 'Table'[category] ), "Belong to Category A", IF ( CALCULATE ( COUNT ( 'Table'[category] ), 'Table'[category] = "A" ) > 0, "Yes", "No" ) ), [MyCount] = 1 && [Belong to Category A] = "Yes" ) )
HI @giorgiokatr
Try this MEASURE. See if its faster
MEASURE = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[customer], "MyCount", DISTINCTCOUNT ( 'Table'[category] ) ), [MyCount] = 1 ) )
thanks @Zubair_Muhammad its perfect but there are many categories in dataset so i would like to have the category a forx example as filter and not only the customers in one category (any one)
Hi @giorgiokatr
Sorry I missed the "a" in your post
Revised MEasure
MEASURE = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[customer], "MyCount", DISTINCTCOUNT ( 'Table'[category] ), "Belong to Category A", IF ( CALCULATE ( COUNT ( 'Table'[category] ), 'Table'[category] = "A" ) > 0, "Yes", "No" ) ), [MyCount] = 1 && [Belong to Category A] = "Yes" ) )
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
94 | |
88 | |
32 | |
28 |
User | Count |
---|---|
153 | |
101 | |
82 | |
63 | |
52 |