Forum Discussion

nh27's avatar
nh27
Icon for Helper III rankHelper III
3 years ago

Create a measure in query editor

I have a column in Table A that I need to return a value based on a calculation using a value from another column in table A and also a value in Table B. 

Table A & B are linked with a one to many relationship but can't seem to figure out how to reference this in the query editor, anyone have any ideas how to achieve this?

DAX

11 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Depends on which end of the relationship Table A is. If Table A is the many side then in a calculated column on Table A you can get a value from Table B using RELATED. If Table A is the one side then you would use RELATEDTABLE along with an iterator like MINX, MAXX.

    • nh27's avatar
      nh27
      Icon for Helper III rankHelper III
      3 years ago

       

      I'm not able to use any of these formula it seems, although I have a one to many relationship established in the power query editor itself for a calculated column.

      So for example, left table is my customer lookup, right table is my data table A.

      I want the COGS column to be a calculation taking "Volume" from data table A * "Cogs Rate" from customer lookup.

       

      The relationship is managed based on the Customer/Material unique key I have created.

      Any ideas?

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        That should just be

        COGS =
'Table A'[Volume] * RELATED ( 'Customer'[Cogs rate] )