Return value from another table with one to many relationship within those two tables

[NoobAnalyst01]

Hello,

I have two tables with the relationship below and sample data as below:

in the ASEET TABLE, for example, how can I added an column the get the value for "region" from ASSET_ATTRIBUTE table,basically need to filter asset_attribute table by type="region" and then get the value where asset_id=asset_id

Thanks

Frank

[v-xjiin-msft · Accepted solution]

Hi @NoobAnalyst01,

To achieve your requirement, please try following method, see if it works for you:

DOMICILE =
CALCULATE (
    MAX ( ASSET_ATTRIBUTE[VALUE] ),
    FILTER (
        ASSET_ATTRIBUTE,
        ASSET_ATTRIBUTE[TYPE] = "REGION"
            && ASSET_ATTRIBUTE[ASSET_ID] = ASSET[ASSET_ID]
    )
)

Thanks,
Xi Jin.

[v-xjiin-msft · Accepted solution]

Hi @NoobAnalyst01,

To achieve your requirement, please try following method, see if it works for you:

DOMICILE =
CALCULATE (
    MAX ( ASSET_ATTRIBUTE[VALUE] ),
    FILTER (
        ASSET_ATTRIBUTE,
        ASSET_ATTRIBUTE[TYPE] = "REGION"
            && ASSET_ATTRIBUTE[ASSET_ID] = ASSET[ASSET_ID]
    )
)

Thanks,
Xi Jin.

[nyeman17]

Thank you for this! This helped me on a separate issue where "LOOKUPVALUE" was not working.

[NoobAnalyst01 · Accepted solution]

Hi Jin,

Thanks,

this works for me.

the only question i have is why in my filter statement, i should include a asset_id=attrtibute_value.

becaue in my relationship, i alredy join those two tables together 1:m relationship.

but when I remove the a asset_id=attrtibute_value in my formula, it doen't work, just wonder is there any reason why we should include this?

Thanks

Frank

[TomMartens]

Hey,

I would recomend that you take a look at the DAX function LOOKUPVALUE

https://msdn.microsoft.com/en-us/query-bi/dax/lookupvalue-function-dax

From a first glance at your 2 tables I would suggest that you consider to restructure your ASSET_ATTRIBUTE table, create new columns from the Type column for all your asset ids.

As far as I can guess all the types are describing  the asset anf for this reason it would make sense to transform your wide table into a long table.

Even if this article was not written with Power BI tables in mind it is the shortes read about tidy data 🙂

https://www.jstatsoft.org/article/view/v059i10/v59i10.pdf

Regards

Tom

[NoobAnalyst01]

there is a alteltive solution i think is duplaice the attribute table for 3 tables, and each table filter one type in the query editor step; then it will be one to one relationship with the main table.

[Zubair_Muhammad]

@NoobAnalyst01

May be

Domicile =
CALCULATE ( FIRSTNONBLANK ( ASSET_ATTRIBUTE[Region], 1 ) )

[NoobAnalyst01]

will try.