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 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:
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.
HI @eFields ,
You can use union function to create a calculated table with all failure tables and build relationship between 'union' table and 'device info' table based on device id.
Failures Info = UNION ( 'Failures 1', 'Failures 2', 'Failures 3' )
After these steps, you can create a matrix visual use device name as row, failure name as column, hits as value and turn on show item with no data on device name.
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
That's what I tried first, but unfortunately when I put that in a matrix it creates staggered rows for each device instead of putting the entries adjacent. So for a system where I have Failures for each type of [2,3,2,6] It creates 13 rows for the device instead of having all the data in 6 rows (At least when I have "show on rows" enabled).
If I leave the failure name as the column then I get a very long matrix (greater than 20x20 to say the least), but the way I need the data presented the final table has to have EXACTLY 8 columns [DeviceName, ID, F1Name, F1Hits, F2Name, F2Hits, F3Name, F3Hits, F4Name, F4Hits].
Hi @eFields ,
Can you please share some sample data so that I can test on it? You can export a part of records to excel file and share them.
Notice: do mask on sensitive data.
Regards,
Xiaoxin Sheng
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 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |