count customers only in one category and not in other

[giorgiokatr]

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?

[Zubair_Muhammad]

HI @giorgiokatr

Try this MEASURE. See if its faster

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

[Zubair_Muhammad]

[giorgiokatr]

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)

[Zubair_Muhammad · Accepted 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"
    )
)

[giorgiokatr]

thanks @Zubair_Muhammad!!