Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic Filter using measure

Hi All,

My source is excel and the data is number of items (count) for each day. I have the counts on a score card. Since it is bringing sum of all the days i added a date filter and choose the latest date from the drop down. I want to do away with this manual selection of the latest dayte every time. I was wondering if there is an easy way , like the filter defaults to the latest data from the excel sheet. I tried creating a measure (max(date)) but there is no option where i can use it as a Filter. Please advice.

Thanks

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi Anonymous ,

     

    1. Mark the largest date.

    2. Set the maximum date as the default value.

     

    Each time the date column is updated, the slicer will select the maximum date by default.

    The same is true for the solutions provided by amitchandak, DataZoe 

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Create a column like this in date table or table and filter that on MaxDate

     

    Is max Date =
    var _max = maxx(Table, Table[Date])
    return
    if('Date'[Date]=_max,"MaxDate",[Date]&"")

     

    You can sort this on date column https://youtu.be/hfn05preQYA

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Amitchandak,

      I think my maxdate is not working, please see the attached screen shot and the calculation i used. My measure 

      MAXDATE = MAXA(test[DATE])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Can you please make it modular. For layman like me , 

      Create a column --- which one is a new column from your example (Is max date = ???)

      Then filter (?????) ok completely locst here , does this whole thing must be used in a filter

      var _max = maxx(Table, Table[Date])
      return
      if('Date'[Date]=_max,"MaxDate",[Date]&"")

       

      Thanks

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    5 years ago

    Anonymous You can use this to have it mark the the latest date in a calculated column, then use that in a slicer:

     

    Latest = if(financials[Date]=CALCULATE(max(financials[Date]),all(financials)),"Current","Past")
     

     

     
     
     
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks DataZoe ,  i want to clarify. First create a  measure and then use the IF condition? please explain.

      Thanks

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    1. Mark the largest date.

    2. Set the maximum date as the default value.

     

    Each time the date column is updated, the slicer will select the maximum date by default.

    The same is true for the solutions provided by amitchandak, DataZoe 

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.