Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

make two relations for two table HELP

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,

 

Need Help
  • hansei's avatar
    hansei
    6 years ago

    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

2 Replies

  • hansei's avatar
    hansei
    Helper V
    6 years ago

    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