Forum Discussion

athomp15's avatar
athomp15
Helper I
8 years ago
Solved

Help with DAX filter

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

 

 

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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's avatar
    v-frfei-msft
    8 years ago

    Hi athomp15,

     

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

     

    Regards,
    Frank

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    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's avatar
    v-frfei-msft
    Community Support
    8 years ago

    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's avatar
      v-frfei-msft
      Community Support
      8 years ago

      Hi athomp15,

       

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

       

      Regards,
      Frank