Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

Are you able to post your PBIX file? 

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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



@Anonymous wrote:

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



 

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.