Help with DAX filter

[athomp15]

Hi All,

Would really appreciate some help on a DAX formula.  I am calculating the number of hits users have to a URLs within a 7 day rolling period.  The measure looks like this.

Rolling 7 Day File Views =
CALCULATE (
SUM(AuditDataTable[Views],
FILTER (
ALL (AuditDataTable[CreationDate]),
AuditDataTable[CreationDate]
>= MAX ( AuditDataTable[CreationDate]) - 7
&& AuditDataTable[CreationDate] <= MAX ( AuditDataTable[CreationDate])
)
)

The measure works fine.  However I am now trying to work out what I need to change to provide number of users (email address) where they have exceeded 20 View on a distinct count of the URL.  I can get some output using the following measure, but as soon as I change SUM(AuditDataTable[Views] to DISTINCT(DataTable[Url]) in the  returns blank.

Threshold Alerts = CALCULATE(COUNTROWS(AuditDataTable), FILTER(AuditDataTable,[Rolling 7 Day File Views] > 20))

Many thanks

Alex

[MFelix · Accepted solution]

Hi @athomp15,

Try to make the DISTINCTCOUNT(DataTable[Url]).

When you use DISTINCT you are returning the values that are distinct in a column so this is a mutiple value measure and will not return what your need, DISTINCTCOUNT will count as the name says.

Regards,

MFelix

[v-frfei-msft]

Hi @athomp15,

Based on your description, I made a sample here and update your formula as below, then your second formula can work well.

Rolling 7 Day File Views = 
CALCULATE (
SUM(AuditDataTable[Views]),
FILTER (
ALL(AuditDataTable),
AuditDataTable[CreationDate]
>= MAX ( AuditDataTable[CreationDate]) - 7
&& AuditDataTable[CreationDate] <= MAX ( AuditDataTable[CreationDate])
)
)

For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your pbix to me.

https://www.dropbox.com/s/qsuujobu8wx0pzp/DAX%20FIL.pbix?dl=0

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @athomp15,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[MFelix · Accepted solution]

Hi @athomp15,

Try to make the DISTINCTCOUNT(DataTable[Url]).

When you use DISTINCT you are returning the values that are distinct in a column so this is a mutiple value measure and will not return what your need, DISTINCTCOUNT will count as the name says.

Regards,

MFelix