Hi!
First of all, I'm fairly new to Power BI, so if I've got a concept completely wrong, or I'm approaching something in a silly way, please do let me know - I'm trying to learn!
So I've got 4 tables; the first two them are "raw" data I am sent once a month, and the last two are tables I've created for the report:
- Table 1 - "Master": a list of incidents with that caused some machine to fail, including a number of details including the machine ID (a unique identifier), the Type (which type of machine it is) and the dates for both the occurence of the incident and when it was fixed.
- Table 2 - "Machine data": a list comprised of information about a given machine and whether or not is it in use. This list is formatted in such a way so that every month, data for every active machine is appended, e.g. the name, the date of the month for which it has been active and a few other details.
- Table 3 - "Active machine data": basically a slice of table 1 including just the last month, to have a table that includes just the machines that are currently active. I created this table to have a table with unique IDs as to create a relationship with the Master table (Table 1) from which I can easilly do analysis.
- Table 4 - "Calendar table": Fairly self-explanatory - a calendar table connected up with the Master table to perform slicing, etc.
What I'm trying to do, and have achieved successfully using a relationship between Table 1 & 3, is calculating and displaying the number of "off days" for each machine for each month. Doing the most recent month is easy, as I'm able to use table 3 which has unique machine IDs meaning that I can create a relationship bewteen it and the master table. However, now that I'm trying to do the same with the historic data, I run into trouble as the duplicated IDs in table 2 doesn't allow for me to create a relationship between it and the master table (which was the whole reason for creating table 2 in the first place).
So, basically my question is: how do I go about this the best way? Should I merge the tables somehow, alter either tables, create a many-to-many relationship, or something entirely different?
Again, apologies for the potentially stupid question and/or explaination. If I was unclear, please tell me what to clarify / expand upon and I'll try my best to do so.
Thanks in advance!
