Hi all!
I have, what I feel should be, a relatively simple problem that I simply cannot figure out. What I have is a table of devices (DeviceInfo) and 4 additional tables (Failures1, Failures2, Failures3, and Failures4) which contain information related to the devices in the first table, but completely unrelated to each other. For any given machine in the DeviceInfo table there may be any number of related entries in the 4 Failure tables (including none). Each of the 4 failure tables contains 2 related values I want to display: FailureName (string) and Hits (int). I've included a sample below to give an idea of what I have:
DeviceInfo Table:
DeviceName | RunId
-------------------------------
Device1 | 1
OtherDevice | 2
LocalHost | 3
RemoteDev1 | 4
RemoteDev2 | 5
etc.
Failures Tables: (All Failure tables have the same format, 2 listed for explanation example)
Failures1 Failures2
RunId | FailureName | Hits RunId | FailureName | Hits
------------------------------ ------------------------------
1 | Bad_Pointer 3 1 | MissingService 62
2 | Bad_Pointer 2 1 | PowerDrain 16
4 | BSOD 4 2 | MissingService 8
4 | Bad_Pointer 8 5 | MissingService 12
etc.
What I am trying to do, but cannot figure out how to, is to create a table that basically looks like this:
DeviceName | RunId | FailureName1 | Hits1 | FailureName2 | Hits2 | Failure3...
-------------------------------------------------------------------------
Device1 | 1 Bad_Pointer 3 MissingService 62
Device1 | 1 PowerDrain 16
OtherDevice | 2 Bad_Pointer 2 MissingService 8
RemoteDev1 | 4 BSOD 4
RemoteDev1 | 4 Bad_Pointer 8
RemoteDev2 | 5 MissingService 12
So basically I am trying to make is a table containing the full list of failures of each type for each system in the minimum amount of space possible. As the failure types are completely unrelated and do not have equal amounts of rows for each RunId, no matter what I try I cannot get this to work.
I have tried using summerize collumns and groupby with filters, but I don't think I am doing them right as I can only ever seem to get the results in a staggered arrangement as opposed to in line.
A couple other important notes on the issue:
- The report is being used to display live data and is using DirectQuery mode, so I cannot alter the data through the query editor (at least not in any meaningful or helpful way)
- If a device has no failures for any of the failure types, I can't have it show up on the final table (as that would dramatically increase the length of the report due to the number of systems which do not fail).
- The 4 failure types must remain seperate (as they are 4 classifications of varying severity)
- This is purely a formatting issue for presentation purposes.
ANY help on this would be immense as I have spent the last 3 days trying to figure out this issue. I am new to both Power BI and DAX and am having a hard time wrapping my head around query languages as opposed to proceedural ones.
Thanks!
BONUS: Additionally, Failures2 and Failures3 CAN have the same failureName (Though they ussually will not). The other FailureNames are only present in that Failure type. If a particular system has the same failure name in both Failures2 and Failures3, I would love to put those entries adjacent to each other on the output table, but this is not necessary.
