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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
paz_23
Regular Visitor

COUNTX (Column with multiple duplicate values)

Hi, 

I am still very new to DAX and I am currently trying to get better with the formulas. My questions is that I have a column [Reference Numbers ] that has various duplicates due to itemization but on the below table I want it to tell me the disitinct reference numbers for the locations for 2021 and 2022. Below are the formula that I currently have but as seen I am getting a result of 902 for 2021 when it should be 395. All help is appreciated! 

 

Screenshot 2023-01-31 104718.png

1 ACCEPTED SOLUTION
olgad
Super User
Super User

Calculate( DISTINCTCOUNT('ClaimsData'[Reference Number]), Filter('ClaimsData', 'ClaimsData'[Year]=2021))

It would be great if you were using a separate data table thats a basis of any report.
Here you can read more on that https://learn.microsoft.com/en-us/dax/calendar-function-dax

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

2 REPLIES 2
olgad
Super User
Super User

Please kindly accept the solution since it worked for you. Thank you


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
olgad
Super User
Super User

Calculate( DISTINCTCOUNT('ClaimsData'[Reference Number]), Filter('ClaimsData', 'ClaimsData'[Year]=2021))

It would be great if you were using a separate data table thats a basis of any report.
Here you can read more on that https://learn.microsoft.com/en-us/dax/calendar-function-dax

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors