Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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,

 

1 ACCEPTED SOLUTION
hansei
Helper V
Helper V

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

View solution in original post

2 REPLIES 2
hansei
Helper V
Helper V

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
Super User
Super User

@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

 

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.