Get The Distinct Count Of Values By Each Day Using DAX

 

We have a serious issue when it comes to calculating the distinct count of values based on dates,

why? cause if you want to get the distinct count for each day, the usual distinct count measure will ignore the day-by-day calculating and will get the distinct count for the whole period. 

so let's see how to overcome this.

The solution is simple ... we will create a summarization by date and we will iterate through it calculating the distinct count

This is the original measure that calculates the distinct count

This is our new adjusted measure it simply defines a variable that holds a summarization of our table by date 
and through this summarization, we will iterate and calculate the sum of the distinct count for each day

Here we can see that for one day the two measures give the same value
and it's reasonable 

This is the tricky part that the new measure overcomes,
for more days, the old measure gets the unique values for the whole bunch of days, not for each day.
when the second one gets it for each day and that's why it gives a larger number

Regards.