cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## count customers only in one category and not in other

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?

1 ACCEPTED SOLUTION
Community Champion

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

Regards
Zubair

5 REPLIES 5
Community Champion

Try this MEASURE. See if its faster

```MEASURE =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[customer],
"MyCount", DISTINCTCOUNT ( 'Table'[category] )
),
[MyCount] = 1
)
)```

Regards
Zubair

Community Champion

Regards
Zubair

Helper V

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)

Community Champion

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

Regards
Zubair

Helper V

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors