Pull values from the many side of a relationship by filtering table

[diegoaranda]

Hi there,

The data model looks like this:

My goal is to pull values FROM the many side of the relationship, meaning that I'd like to bring the "number_value" values from "bi equipment_attribu..." table where [label] = "Minimum Ambient Temperature" into the "Asset Register" table.

The "bi equipment_attribute" table contains these columns of interest:

- equipment_id: to navigate across tables

- number_value: the values I'd like to bring to table Asset Register

- label: the column I'd like to use as filter

Please note that "bi equipment_attribute" table looks like this:

My first attempt looks like this:

After checking Tom's response I realised that "you can't use LOOKUPVALUE in combination with a virtual table". So, what could be a good way to do this?

Cheers 😉

[v-yanjiang-msft · Accepted solution]

Hi @diegoaranda ,

According to your description, I create a sample.

bi equipment_attribute table:

bi equipment table:

Asset Register table:

As the relationship direction is single (1 to many), one side cannot directly reference data from many side. It should be indicated in the formula.

Here's my solution, create a calculated column.

Minimum Ambient Temperature =
CALCULATE (
    MAX ( 'bi equipment_attribute'[number_value] ),
    FILTER (
        'bi equipment_attribute',
        'bi equipment_attribute'[label] = "Minimum Ambient Temperature"
            && 'bi equipment_attribute'[equipment_id]
                = EARLIER ( 'Asset Register'[equipment_id] )
    )
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[diegoaranda]

Hi there,

This is exactly what I needed.

Thanks!

[TomMartens]

Hey @diegoaranda ,

From what I see you want to push a value into a table that's on the many side of a relationship.

For this task, I always use the Function RELATED() inside a calculated column on the many side of a relationship: RELATED( 'tableontheoneside'[columnIwant] ). Here you will find more information about the function RELATED - DAX Guide.

I only use LOOKUPVALUE when there is no relationship between tables. Here you will find more information about the function LOOKUPVALUE - DAX Guide
You also have to consider that you can't use LOOKUPVALUE in combination with a virtual table, indicated by the error message.

Hopefully, this provides what you are looking for and helps to tackle your challenge.

Regards,

Tom

[diegoaranda]

Hi Tom,

I'm sorry if I didn't explain myself correctly. My goal is to pull values FROM the many side of the relationship, meaning that I'd like to bring the "number_value" values from "bi equipment_attribu..." table where [label] = "Minimum Ambient Temperature" into the "Asset Register" table. "bi quipment" table is just in the middle. All three tables are linked through "equipment_id". Assume that when filtering by [label] = "Minimum Ambient Temperature" it would create a 1:1 relationshipo with "Asset Register", I just don't know how to bring the values once the table is filtered.

I understand now that LOOKUPVALUE will not work with a virtual table. I tried the following but also didn't work. I think I have not understand how to bring values from the many side of the relationship.

Thanks again!