Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to filter a table based on user input?

Example - if a user inputs date (from table 1), I need to filter table 2 by displaying all the rows which have the range of date to date - 13.  I created a slicer and now I take the value that is ...
Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    Hi Anonymous ,

    No worries. Yes that can be done. 
    Just create a new measure like below :

     

    _val =

    var _sel = SELECTEDVALUE(dates[Dates])

    var _cond = if (MAX(SampleData[Date]) >= _sel-13 && MAX(SampleData[Date]) <= _sel , 1, 0)

    return
    _cond
     
    Then add this measure as a filter on the visual and set its value to 1

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

rohit_singh's avatar
rohit_singh
Solution Sage
4 years ago

Hi Anonymous,

You can try something like this based on the sample data below:

Table 1

Table 2 


Create a measure on Table 2 that takes the selected value from Table 1 and filters Table 2 using that.

_Dates =

var _sel = SELECTEDVALUE(dates[Dates])

var _mindt =
CALCULATE(
min(SampleData[Date]),
filter(SampleData,
SampleData[Date] = _sel -13
)
)
Return
_mindt
 
 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hey, thank you so much for replying! 
I see what you've done. Is there anyway to get 14 - 27th dates in a table according to your example above?

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hi Anonymous ,

    No worries. Yes that can be done. 
    Just create a new measure like below :

     

    _val =

    var _sel = SELECTEDVALUE(dates[Dates])

    var _cond = if (MAX(SampleData[Date]) >= _sel-13 && MAX(SampleData[Date]) <= _sel , 1, 0)

    return
    _cond
     
    Then add this measure as a filter on the visual and set its value to 1

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much! I'm going to try this out right now!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Also, if I had to save this filtered table and then join with another table and display that in the report - can I do that?
      so, everytime a user selects a date, I filter the table and perform a join with another table and display that in the report. Is this possible?

       

      • rohit_singh's avatar
        rohit_singh
        Solution Sage
        4 years ago

        Hi Anonymous ,

        You don't need to save the table. You can smply create a relationship between Table2 and the other table and drag and drop columns from the other table into your visual. The values in the otehr table will be filtered automatically based on the relationship you've created.

        Kind regards,

        Rohit

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I'm not sure what I'm doing wrong. But when I do the same thing, I don't get the same results.
      I even made the same dummy data as you did to check. I created _val in the second table 
      This is what happens.


      I don't know why _dtmin and _dtmax are taking the selected dates because i did - _dtmin = MIN(SampleData[Date}) 


      Can you tell me what I'm doing wrong please? Thank you

      • rohit_singh's avatar
        rohit_singh
        Solution Sage
        4 years ago

        Hi Anonymous ,

        Create the measures as follows :

        1) _dtmax = SELECTEDVALUE(dates[Dates])
        2) _dtmin =

        var _sel = [_dtmax]

        var _mindt =
        CALCULATE(
        min(SampleData[Date]),
        filter(SampleData,
        SampleData[Date] = _sel -13
        )
        )
        Return
        _mindt
         
        3)  _val =

        var _sel = SELECTEDVALUE(dates[Dates])

        var _cond = if (MAX(SampleData[Date]) >= _sel-13 && MAX(SampleData[Date]) <= _sel , 1, 0)

        return
        _cond