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,User2,Val3

01/03/2020,User3,Val1
01/04/2021,User3,Val2
01/06/2021,User3,Val3
01/03/2022,User3,Val4

 

The Date column is a validity date. 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

 

For  this, I setup a new table, filtering the existing User table with the following DAX Query.

FilteredUser = VAR D1= DATE(2021,07,01)
RETURN FILTER(ADDCOLUMNS(User, "rank", RANKX(FILTER(User, User[Date]<=D1 && [User] = EARLIER([User])), [Date],[Date],DESC,Dense)),[rank]==1 && User[Date]<=D1)
 
I add a rank column base on the date, and filter only rank==1, removing line also behind the validity.
This is working well using a constant date in the DAX formula.
 
What I would like now is to put a date slicer into my PBI.
And use the selected value of this slicer in my DAX expression.
 
I've create for this an independant Date Table. And bind my slicer to this date Table.
I've try to modify my DAX formula with something like:
Table1 = VAR D1= SELECTEDVALUE('Date'[Date])
RETURN FILTER(ADDCOLUMNS(User, "rank", RANKX(FILTER(User, User[Date]<=D1 && [User] = EARLIER([User])), [Date],[Date],DESC,Dense)),[rank]==1 && User[Date]<=D1)
 
Also try with 
VAR D1= CALCULATE ( MAX ( 'Date'[Date] ) )
 
But find no way to make it work.
It seems that I can't get the slicer value in this context.
 
Do you have any idea of how I can make this working ?
Here the PBI I use: 
 
Best regards,
            Laurent.
 
 
 
 

 

 

 

  • 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

6 Replies

  • 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.

       

  • 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