make two relations for two table HELP

[Anonymous]

Hey, 

I have two tables, one is an alarmlist with a specific system message for an alarm, and the other is a table with all alarms that have been triggered, what alarm, time and duration and for what machine. 

The problem I have is that there are several different machines, and every machine has it own alarmlist, and the alarmlist have an ID for every alarm, and AlarmID 1 exist on all machines for example. I merged all alarm lists together, and added a MachineID for every machine. 

So I have Alarmlist forexample, (note that an AlarmID for on machine doesn't have to mean the same for another): 

AlarmID; System message; MachineID

2000; error slow; 1

2000; error fast; 2

2000; error medium; 3

And the table with the triggered and normal alarms:

AlarmID; MachinedID; ActiveAlarm; Time; Duration

0; 1; 0; 07:40; 2

2000; 1; 1; 07:42; 3

I want to combine theese tables in a visualisation that shows alarmID, machineID, systemmessage time and duration. 

But I am having trouble linking both MachineID and AlarmID. I can only have one active link. If I only link machineIDs I get all systemmessage. 

Dont know How To do this? Also for the table with triggered alarms are there a alarm when there a no alarm, a normal state which have alarmID 0, and this one doesn't have a systemmessage in larmlist, that is just triggered alarms. 

Appreaciate help on how to do this. 

Kind regards,

[hansei · Accepted solution]

Combine your columns in M or DAX

M: Table.AddColumn(alarmlist, "FullID", Text.Combine({Text.From("AlarmID"),Text.From("MachineID")})

DAX: alarmlist[FullID] = "" & [AlarmID] & [MachineID]

Do this for both tables and create your relationship between the 2 [FullID] columns, which should have be many-to-one events-to-alarmlist

Events that do not have a corresponding alarmlist entry (like when alarm ID = 0) will show up as (Blank) error codes

[amitchandak]

@Anonymous ,

There is two way. One is to append data - https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Another one is to create common dimensions using union and distinct and join them with these tables

machine = distinct(union(all(table1[machine Id]),all(table2[machine Id])))

Do same for other dimesnions