The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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:
Thanks!
Hi,
Assuming you have:
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.
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!
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.
Hi,
Share the link from where i can download your PBI file.
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-
Hi,
I tried but could not solve it.
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
157 | |
137 | |
132 | |
81 | |
61 |