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...
DAX
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] )
  • nh27's avatar
    nh27
    Icon for Helper III rankHelper III
    3 years ago

    I just cannot seem to reference the Customer_Lookup table in this formula, only Table A, not sure why as the relationship is there in place.

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

      Is it giving an error? If there are squiggly red lines in the formula bar it could just be Intellisense playing up.

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

        This is the error I am getting