how to count distinct in power bi

[phalondon]

hi every one,

i am a newbie with Power BI. can you please help me with this count calculation?

i have this data

date	user	product	sale
1/1/2023	A	cola	1
1/2/2023	B	cola	1
1/2/2023	C	cola	1
1/1/2023	D	cola	1
1/1/2023	D	cola	1
1/2/2023	E	cola	1
1/2/2023	F	cola	1
1/2/2023	E	pepsi	1
1/2/2023	F	pepsi	1

now i want to count howmany user sell the same number of bottle in Jan and Feb 2023. the result i would like to have is:

thank you

month	number of bottles	number of user
1.2023	1	1
1.2023	2	1
2.2023	1	2
2.2023	2	2

[Anonymous]

hello

[Anonymous · Accepted solution]

Hi @phalondon ,

Please try below steps:

1. below is my test table

Table:

2. add a new column with below dax formula

month = YEAR([date])&"."&MONTH([date])

3. create a measure with below dax formula

Measure =
VAR cur_month =
    SELECTEDVALUE ( 'Table'[month] )
VAR cur_sale =
    SELECTEDVALUE ( 'Table'[sale] )
VAR tmp =
    CALCULATETABLE (
        VALUES ( 'Table'[user] ),
        FILTER ( ALL ( 'Table' ), [month] = cur_month && [sale] = cur_sale )
    )
RETURN
    COUNTROWS ( tmp )

4. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.