Forum Discussion

dimes's avatar
dimes
Icon for Helper I rankHelper I
6 years ago

Slicer filter shows distinct table values

Hello,

following scenario... I have a project list with a date field "last change":

 

 

then I have created a calculated date table:

Now I want to use this date as a slicer and only show the latest project entry (only one line) before the selected date. For example, if I select 01.05.2020 I want only see the yellow marked line:

 

 

How can I achieve this?

Need Help

11 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    6 years ago

    dimes 

     

    Create a measure

    FilterMeasure = 
var _selectedDate= SELECTEDVALUE('Date'[Date])
var _selectedLastchange= SELECTEDVALUE(Projects[Last change])
var _max= MAXX(FILTER(ALL(Projects[Last change]),Projects[Last change]<_selectedDate),Projects[Last change])
var _result= IF(_selectedLastchange=_max,"show","hide")
return IF(ISBLANK(_selectedDate),"show",_result)

     

    Then apply this measure as a visual level filter. 

     

    You can remove this measure from value field, just need to keep it in the filter field. 



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

    • dimes's avatar
      dimes
      Icon for Helper I rankHelper I
      6 years ago

      nandukrishnavs It goes in the right direction! 

       

      If I select e.g. 08.05.2020 it works:

      if I select a data in between e.g. 01.05.2020 I would expect the second entry... 

       

      here is my measure calculation:

       

       

      Snapshot_filter 2 = 
var _selectedDate= SELECTEDVALUE('Date'[Date])
var _selectedLastchange= SELECTEDVALUE(project[Date_Of_Last_Change])
var _max= MAXX(FILTER(ALL(project[Date_Of_Last_Change]);project[Date_Of_Last_Change]<_selectedDate);project[Date_Of_Last_Change])
var _result= IF(_selectedLastchange=_max;"show";"hide")
return IF(ISBLANK(_selectedDate);"show";_result)

       

       

      • nandukrishnavs's avatar
        nandukrishnavs
        Icon for Community Champion rankCommunity Champion
        6 years ago

        dimes 

         

        Try this

         

        Snapshot_filter 2 = 
var _selectedDate= MIN('Date'[Date])
var _selectedLastchange= SELECTEDVALUE(project[Date_Of_Last_Change])
var _max= MAXX(FILTER(ALL(project[Date_Of_Last_Change]);project[Date_Of_Last_Change]<_selectedDate);project[Date_Of_Last_Change])
var _result= IF(_selectedLastchange=_max;"show";"hide")
return _result

         



        Did I answer your question? Mark my post as a solution!
        Appreciate with a kudos         🙂

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    I believe you will need what I call a complex selector for this. It is based on my Inverse Selector found here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290.

     

    I have attached an example. Basically in your case, you would grab the date like:

     

    Latest Date = MAXX(FILTER(ALLEXCEPT('Table',[Project ID]),[Last changed] < MAX('Calendar'[Date]),[Last changed])

     

    So, reading that in English, get all of your table except respect the filter on Project ID. Now filter that table to where the Last changed column is less than your selected value for calendar date. Grab the maximum value for Last changed, which is the latest date that is less than your selected date.

     

    You would compare this measure to the current value of the date line and if they are equal return 1, otherwise 0. You then use this as your filter.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    dimes , try a measure like

    Measure =
    Var _max =maxx(allselected(Table),Table[Date])
    return
    calculate(max(Table[Score]), filter(all(Table),Table[date]<=_max && Table[date] = _max) =lastnonblankvalue(Table[project_id],max(Table[Date])))

     

    Add that to visual