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

ManguilibeKAO's avatar
ManguilibeKAO
Icon for Resolver I rankResolver I
4 years ago

Hi,

 

When you write: " Let say for exemple that we are in July 2021, i want to filter the table to only keep the following lines:

User1, 01/01/2021, Val2

User2, 01/02/2020, Val1

User3, 01/06/2021, Val3", could you you explain your filtering logic (the criteria of filtering)?  If the criteria of filtering are clear, we can help you.

 

Best regards.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

To clarify the criteria.

This is a table with historical data.

When you made calculation base on the table, you set a date [dt1] somewhere in the past.

The line to apply then for each user is the first one older to [dt1].

 

So if I come back to my example, with a [dt1] set to 5/1/2021, you will keep in final result only those lines:

01/01/2021,User1,Val2

01/02/2020,User2,Val1

01/04/2021,User3,Val2

 

Hopes it make it clearer like this.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello,

       

      Sorry for the delay in my response, was on another stuff last weeks.
      I test your solution today, it works great.
      Exactly what I need.

      Thanks a lot,

             Laurent.