Forum Discussion
Filter Contents of one Table based on another Table
Hi everyone,
I've tried to do some searches on my challenge, but had no luck. Measures are still fuzzy to me, so it probably doesn't help that I don't have the right vocabulary.
I have a table that lists a number of files and the servers they are on. The table includes an ID for each file, but each file may be listed more than once as it could be on multiple servers (think data caches).
I have another table that lists events for each file, again, multiple occurences of the same ID because each file has multiple associated events. In the events table I have a calculated measure that identifies the most recent (latest) event. This table also has a date-timestamp for each event as well as other data about the event.
I want to create a measure that will allow me to include the date-timestamp value of the most recent event in a column in a table listing all of the files in their location. so that table would look like this:
File ID (table1) File Name (Table 1) File Server (Table 1) Date of Most Recent Event (table 2) Most Recent
User (table 2)
05 MyFile5 NewYork 2018/03/31 rpiboy
07 MyFile7 New York 2018/04/05 jdoe
05 MyFile5 Boston 2018/03/31 rpiboy
07 myFile7 Philadelphia 2018/04/05 jdoe
9 Replies
Would merging your two tables be an option? Depending on the relationship you can merge the tables on the one side of the relationship. The video below is a helpful tutorial.
No, I don't think Merge would be appropriate here, because keep in mind the straight data in the two tables is a many to many relationship. Its only after applying the calculated measure for the most recent (latest) event that the data is now a many to one. I.E. one Event for each Unique File in the File Table. Keeping in mind that in the File table each file can have more than one occurence, as the Index in the File Table is the combination of File Name & Server Name. In my overall data model there is not even a direct relationship between the two tables for the reason, they're indirectly related by virtua of a third table (File Details).
Another way to think about my issue would be that I have two tables with a many to many relationship (if there were a direct relationship). Using a Measure I can get reduce the data down to where it would become a many to one relationship. However, I need a way to bridge the two tables together (I would assume with another measure) so that I can visualize the data together in a single visual context.