Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Wanted to bump this up, in the past had some very helpful responses to my queries.
Are you able to post your PBIX file?
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.
Am I missing something? We can't attach files to Posts in the forums?
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?
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...
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |