Need help with count no of customer

[Anonymous]

Hello,

Can you please help me writing following DAX formula:

I want to calculate  no of customer who have both product A and B only as Measure. Can you please advise me on it. Thanks!

Customer ID	Product Type
1	A
1	A
1	B
2	A
2	B
2	B
3	A
4	B
5	B

[amitchandak · Accepted solution]

@Anonymous , Try like

countx(filter(summarize(Table, Table[Customer], "_1", calculate(distinctcount(Table[Product Type]), filter(Table,[Product Type] in {"A", "B"}))), [_1]=2),[Customer])

[v-luwang-msft · Accepted solution]

HI  @Anonymous ,

Try the below measure:

test =
VAR test1 =
    IF (
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Product Type] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Customer ID] = MAX ( 'Table'[Customer ID] ) )
        ) > 1,
        1,
        0
    )
RETURN
    test1

sumcount = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]),FILTER('Table',[test]>0))

And final get:

Wish it is helpful for you!

Best Regards

Lucien

[amitchandak · Accepted solution]

@Anonymous , Try like

countx(filter(summarize(Table, Table[Customer], "_1", calculate(distinctcount(Table[Product Type]), filter(Table,[Product Type] in {"A", "B"}))), [_1]=2),[Customer])