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.
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,
Solved! Go to 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
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
@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
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |