cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sbalish
Frequent Visitor

Distinct count total over date range

When using DISTINCTCOUNT to get total unique numbers over a date range (4 months for example)...the total count when adding up all 4 months shown on the chart is higher (incorrect) compared to the correct count (400) when just the year is shown on the chart.  Pretty sure this is becasue displaying the chart with the 4 month hierarchy forces a distinct count in each month separately so the same number gets counted multiple times if it appears in multiple months.  How can I get the chart displaying all 4 months to show the correct total (400) by only counting a number once regardless of if it's used in multiple months?  I'm not concerned about which month will show the count for numbers used in multiple months...first month it's used in is fine...just want to only count it once with no repeats. 

 

DAX used:

 

Tasks Started = CALCULATE(DISTINCTCOUNT('Tasks'[Number]))

 

DC_month.jpgCount total 417 (incorrect)DC_year.jpgCount total 400 (correct)

Thanks!

6 REPLIES 6
Super User IV
Super User IV

Re: Distinct count total over date range

Hi,

Assuming you have:

  1. A Calendar Table with a relationship from the Date column of your Tasks Table to the Date column of your Calendar Table
  2. In the Calendar Table there will be a Year and Month column
  3. To your visual, you have dragged Year and Month from the Calendar Table

Write these measures

Distinct tasks = DISTINCTCOUNT('Tasks'[Number])

Distinct tasks final = COUNTROWS(FILTER(SUMMARIZE(VALUES('Tasks'[Number]),'Tasks'[Number],"Count since inception",CALCULATE([Distinct tasks],DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar[Date]),Calendar[Date]),MAX(Calendar[Date]))),[Count since inception]=1))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sbalish
Frequent Visitor

Re: Distinct count total over date range

Hi! Thanks for the tip.  Yes to all 3 assumptions.  Added the Distinct Task Final measure...error popped up expecting another argument for the FILTER command...I think it needs a FilterExperssion?  Also...it was underlining the [Count since inception]=1 part at the end...do I need to add that as a new column in my table?

 

Thanks!

sbalish
Frequent Visitor

Re: Distinct count total over date range

I see it...needed another right ) just after the MAX(Calendar[Date] part...

 

Distinct tasks final = COUNTROWS(FILTER(SUMMARIZE(VALUES('Tasks'[Number]),'Tasks'[Number],"Count since inception",CALCULATE([Distinct tasks],DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar[Date]),Calendar[Date]),MAX(Calendar[Date])))),[Count since inception]=1))

 

Unfortunately this measure returns the same count totals.

DC_month2.jpgTasks Started Final (417...still incorrect)

Highlighted
Super User IV
Super User IV

Re: Distinct count total over date range

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sbalish
Frequent Visitor

Re: Distinct count total over date range

Hi...sorry had to rebuild it with a smaller data set.  Here you go!

 

https://www.dropbox.com/s/2o9shbq1ss95t0b/Distinct%20Count%20Range%20Test.pbix?dl=0

 

Thanks again for you help.

 

SB-

Super User IV
Super User IV

Re: Distinct count total over date range

Hi,

I tried but could not solve it.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors