Forum Discussion

NoobAnalyst01's avatar
NoobAnalyst01
Icon for Helper I rankHelper I
8 years ago
Solved

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

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

Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

  • NoobAnalyst01's avatar
    NoobAnalyst01
    8 years ago

    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

7 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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.

    • NoobAnalyst01's avatar
      NoobAnalyst01
      Icon for Helper I rankHelper I
      8 years ago

      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

    • nyeman17's avatar
      nyeman17
      Frequent Visitor
      2 years ago

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

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      NoobAnalyst01
      Icon for Helper I rankHelper I
      8 years ago

      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.