How to filter/Drill-through on column value on same page

[Bertenvanloover]

Hi all,

I'm looking for a way to do the following: when a user clicks on a row of table1 he'd get a filtered overview of all statusupdates in table2 of that filename on the same page. This way the user can analyse why a certain statusupdate took so long.

The issue I have is that table2, logically, only shows that specific statusupdate of table1.

Table1 shows the statusupdates that had the longest waiting time (does not have to be table, as long as you can clearly spot the top10 files), Table2 is then used to show all the details of that file.

Table1

Month	Filename	Waiting time statusupdate (days) (Z-A)
March	123	11
March	456	10
February	789	9
March	123	8
January	999	7

Table2 (to be situation when user selects the first row with 123 in it)

Filename	Date (Z-A)	Statusupdate	Waiting time statusupdate (days)
123	March 10	Approved	8
123	March 9	Send for Approval	11
123	February 8	Editing	2
123	February 6	Initiated	1

what I see as Table2

Filename	Date	Statusupdate	Waiting time statusupdate (days)
123	March 9	Send for Approval	11

I've been looking around, though I did not find the process and I presume it is fairly straightforward?

Thanks a lot!!

[vanessafvg · Accepted solution]

how are you joining the data between the tables?

my suggestion and this is the best way to model transactional data in different tables..

create a new table call file and in that put all the unique filenames.  No duplicates

then join both table 1 and 2 to that table.. see attached, you then need a slicer to select the file name

there is another way to do it but this is probably the best practise way.  

the other way is you can create a bidirectional relationship betwen the filename table and table 2 and then if you click on the table1 it filters table2 but its not recommended to use bidirectional relationships unless you understand the implications.  In order to filter by filename only you need to provide that as the filter context, filtering by the row will not filter the filename only, it will only use the row context to filter.  this is why modelling this data properly is important.

see file attached.

[Bertenvanloover]

Hi Vanessa, since my report contains >10.000 files the added filter doesn't really work that smoothly. 
What implications am I missing on the bedirectional relationship? Also I don't think I can use this since these tables are just a visualisation on the Report tab and therefore are not present in the Model tab.

Also both tables are made out of the exact same data. Table1 is just a summarized version of Table2 to give the user an overview of highest waiting times. Thus Filename of Table1 is the exact same datapoint as Filename in Table2..

[vanessafvg]

ok so you have then not given enough information to provide you the correct solution.

The key thing is in order to do what you need to do, you need to model this data correctly, all answers lie in how you are modelling the data.    

please provide a proper explanation of what you are trying to achieve, with a copy of what your model looks like and an example of what you are trying to achieve.

[Bertenvanloover]

Hi Vanessa, while building a sample report from scratch I noticed that the sample did do what I wanted it to do. So now I learned to that a page filter was obstructing this procedure. So luckily it is solved, unfortunately it was my own mistake. Have a nice weekend

[vanessafvg]

glad you could resolve this!