cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rpiboy1 Frequent Visitor
Frequent Visitor

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 9
Anonymous
Not applicable

Re: Filter Contents of one Table based on another Table

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.

 

https://www.youtube.com/watch?v=hVWSxX-uF-0

rpiboy1 Frequent Visitor
Frequent Visitor

Re: Filter Contents of one Table based on another Table

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

rpiboy1 Frequent Visitor
Frequent Visitor

Re: Filter Contents of one Table based on another Table

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.

rpiboy1 Frequent Visitor
Frequent Visitor

Re: Filter Contents of one Table based on another Table

Wanted to bump this up, in the past had some very helpful responses to my queries.

Anonymous
Not applicable

Re: Filter Contents of one Table based on another Table

Are you able to post your PBIX file? 

rpiboy1 Frequent Visitor
Frequent Visitor

Re: Filter Contents of one Table based on another Table

I'm not... it has far to much business sensitive data in it.

 

I could potentially mock up a much more simplified version, but that will take a few days. Its likely a worthwhile exercise though...

rpiboy1 Frequent Visitor
Frequent Visitor

Re: Filter Contents of one Table based on another Table

Am I missing something? We can't attach files to Posts in the forums?

Anonymous
Not applicable

Re: Filter Contents of one Table based on another Table

You have to upload the file to an online storage site like onedrive, dropbox, etc. then you can post the link to the file. 



@rpiboy1 wrote:

Am I missing something? We can't attach files to Posts in the forums?



 

rpiboy1 Frequent Visitor
Frequent Visitor

Re: Filter Contents of one Table based on another Table

Alright, here is the sample/example. Complete junk data, but the structure and relationships match what I have in the real data. Put some notes in the PBIX to clarify the goal.

 

Sample Data on Google Drive

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)