Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need help filtering data using DAX & TimeSlicer,

Hello All,   I have the following User table:   Date, UserKey, Value 01/01/2019,User1,Val1 01/01/2021,User1,Val2 01/01/2022,User1,Val3 01/02/2020,User2,Val1 01/08/2021,User2,Val2 01/10/2022...
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi， Anonymous 

    Please try follow steps:
    1.create measue as below:

     

    lastdate = 
VAR D1 =
    DATE ( 2021, 05, 01 )
RETURN
    CALCULATE (
        MAX ( User[Date] ),
        FILTER ( ALLEXCEPT ( User, User[User] ), User[Date] <= D1 )
    )
//Group by user and get max date less than or equal to current date
    visual filter = IF(SELECTEDVALUE(User[Date])=[lastdate],1,0)

     

    2.then drag  the measure 'visual filter' to the filter panle of this table visual as below:

    Best Regards,
    Community Support Team _ Eason

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

Calculated tables are not affected by slicers or filters as they are only calculated during data refresh. To achieve what you're after you could try creating a measure like

Show Data = IF( SELECTEDVALUE('User'[Date]) <= SELECTEDVALUE('Date'[Date]),1,0)

and use that as a visual filter, to only show when the value is 1

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello,

     

    Thanks for your quick answer.

    Got it about the fact that calculated tables are not affect by slicer, and same for caculated column.

     

    I try using a measure, but can't see how to achieve what I want.

    A simple if condition will not work in my case because I want to keep not all line before the date, but the only first one before the date. Let say the date is for exemple 01/05/2021, it will filter for User3 only the line "01/04/2021,User3,Val2".

     

    It was why i've used in my first exemple the Rank function that enable me to give an order on the value to do something like a topN filter. I can't see how I will be able to do the same using a measure.

     

    Laurent.