How to filter a table based on user input?

[ak1925]

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 inputted in a measure. Table 2 has a date column. I'm just not sure how to display table 2 based on the measure to measure -13 values. 

Can you help me out?

[rohit_singh]

Hi @ak1925,

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! 🙂

[ak1925]

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 · Accepted solution]

Hi @ak1925 ,

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! 🙂

[ak1925]

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]

Hi @ak1925 ,

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

[ak1925]

Hey, thanks for the reply. I did exactly as told. But even then when I select 27th, I see all the values.

[ak1925]

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]

Hi @ak1925 ,

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

[ak1925]

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

[ak1925]

Thank you for replying. But doesn't that just filter the table such as the rows will represent the date selected?
I want to display the rows that are in the range of date - 13 and date

[Hariharan_R]

Hi @ak1925 

Build one date table and connect them through date column like below. if you use the date table date column in slicer then table 2 results will automaticlly filtered for the selected date.

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin