Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
phalondon
New Member

how to count distinct in power bi

hi every one,

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

i have this data

 

dateuserproductsale
1/1/2023 Acola1
1/2/2023 Bcola1
1/2/2023 Ccola1
1/1/2023 Dcola1
1/1/2023 Dcola1
1/2/2023 Ecola1
1/2/2023 Fcola1
1/2/2023 Epepsi1
1/2/2023 Fpepsi1

 

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

monthnumber of bottlesnumber of user
1.2023 11
1.2023 21
2.2023 12
2.2023 22
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @phalondon ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1678413520091.png

2. add a new column with below dax formula

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

vbinbinyumsft_1-1678413568123.png

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

vbinbinyumsft_2-1678413661797.png

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

hello

 

Anonymous
Not applicable

Hi @phalondon ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1678413520091.png

2. add a new column with below dax formula

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

vbinbinyumsft_1-1678413568123.png

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

vbinbinyumsft_2-1678413661797.png

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors