How to select the value from another table when critaria matched ?

[susheeltyagi]

Hi
I am new in Power BI. I am struggling with one query. I have two tables Measurement and Alert in power BI. I want to add one custom column in Alert table. The value in Alert table is select from Measurement table based on criteria matched (Device Guid).
Example If both table the Device Guid matched then select Instruement serial number from Measurement table and add into Alert table).

Expected Alert Table should be as below :

ex: in something like in sql [ Select Measurement.InstrumentSerialNumber INTO Alert from Measurement
Where Measurement.DeviceGuid == Alert.DeviceGuid]

How to fix this issue ?

Thanks

Susheel

[Ajinkya369]

hI @susheeltyagi ,

Merge the table alert with Measurement table using left outer join/inner join and at the time of expanidng the column only select the Instrument serial column.To merge, In power Query editor Go to Home -> Combine -> Merge 

Select the common field from both the tables at the time of merging.Refer the image below.

Now you will get a column added into the table something like this:

 Now expand the column by clicking on the expand icon and then select only the Instrument serial column

After clicking on Ok your table is ready

If your problem is solved you can accept this reply as a solution

Thanks

Ajinkya

Analytics Specialist

Master Data Analysis - 

[ibarrau]

Hi. If you have the tables related in the data model, DAX can understand the relationship between them. If and alert has only one measure ( many to one relationship: alert * - 1 measurement) then it's and easy new calculated column in alert like RELATED(Measurement[InstrumentSerialNumber]). However if you have a many to many relationshio between them like the picture suggest you will have a problem. Imagine that table measurement has two instrument code 1 and 2 with a deviceguid of 123. Which instrument code should alert use for the deviceguid of 123? Power BI won't know which one to get unless you aggregate it. I'm not sure if you want that.

Let us know if this make sense and your thoughts to continue.

Regards,

[susheeltyagi]

Hi thanks for reply. Actually the Measurement and Alert has the the relationship on the bases of DeviceGUID and Many to Many. The actual number of fileds in both the tables are more.

The actual Measurement table have somethig like below. I have trid the alculated column in alert like RELATED(Measurement[InstrumentSerialNumber]). But it's not working.

Still my problem has not resolved. Any other option?

[ibarrau]

If you have *-* you have to decide your aggregation because an alert row knows more than one row from measurement. That's why related won't work. You can try something like this as example. Please understand that this dax is an example of the max of the values that might no be what you need for the column.

NewColumn in alert=
MAXX(
    RELATEDTABLE(Measurement),
    Measurement[InstrumentSerialNumber]
)

This will show in alert the max instrument for that specific deviceguid. Try to think which deviceguid you would need to understand the logic to run inside alert.

Regards,