Count of distinct values per day over a number of days

[Coffeeaddict]

I am trying to create a measure to count the number of distinct values of a field per day over a number of days.  

Given a sample data set of 

I am trying to create a measure that when looking at the 3 days will return a value of 5 based on customer (2+1+2)

I cannot use any calculations which will not work with RLS.

[v-kelly-msft]

Hi  @Coffeeaddict ,

Is your issue solved?Did @HotChilli 's suggestion help?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Coffeeaddict · Accepted solution]

Due to the description of SUMX i dont think it can be used with row level security which will be added to the dataset later so the suggestion cannot be used.

This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

I solved this be creating a merged colum of customer id and date to create a field i could do a distinct count on.

[v-kelly-msft]

Hi @Coffeeaddict ,

Great! Could you pls mark the reply as answered to close it?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the day column of your data table to the date column of your calendar table.  Create a date slicer from your Calendar table and select any date range.  Try this measure

=sumx(summarize(values(calendar[date]),calendar[date],"ABCD",distinctcount(data[customer])),[abcd])

Hope this helps.

[HotChilli]

Looks like a good case for a nested measure:

CDistinct = DISTINCTCOUNT(TableJJ[Customer])

Use the above like this:

MeasureQQ = SUMX(DISTINCT(TableJJ[day]),  [CDistinct]  )