cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eFields Frequent Visitor
Frequent Visitor

Creating a table of unrelated columns with a related index and keep null values with Direct Query

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.

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Creating a table of unrelated columns with a related index and keep null values with Direct Quer

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
eFields Frequent Visitor
Frequent Visitor

Re: Creating a table of unrelated columns with a related index and keep null values with Direct Quer

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

Community Support Team
Community Support Team

Re: Creating a table of unrelated columns with a related index and keep null values with Direct Quer

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |