topic Re: Create a measure in query editor in DAX Commands and Tips

Create a measure in query editor

nh27 — Tue, 25 Apr 2023 14:28:43 GMT

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?

Re: Create a measure in query editor

johnt75 — Tue, 25 Apr 2023 15:13:04 GMT

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.

Re: Create a measure in query editor

nh27 — Wed, 26 Apr 2023 16:53:50 GMT

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?

Re: Create a measure in query editor

johnt75 — Thu, 27 Apr 2023 08:24:41 GMT

That should just be

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

Re: Create a measure in query editor

nh27 — Thu, 27 Apr 2023 14:50:48 GMT

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.

Re: Create a measure in query editor

johnt75 — Thu, 27 Apr 2023 14:55:53 GMT

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

Re: Create a measure in query editor

nh27 — Thu, 27 Apr 2023 15:00:08 GMT

This is the error I am getting

Re: Create a measure in query editor

johnt75 — Thu, 27 Apr 2023 15:01:57 GMT

That's in Power Query, its supposed to be in DAX. Select New Column from the modelling tab and put the formula in there.

Re: Create a measure in query editor

nh27 — Thu, 27 Apr 2023 15:03:23 GMT

I should also mention I am trying to create this custom column inside the data table itself

Re: Create a measure in query editor

nh27 — Thu, 27 Apr 2023 15:06:01 GMT

I'm trying to achieve this in Power Query in the editor, is it not possible?

Re: Create a measure in query editor

johnt75 — Thu, 27 Apr 2023 15:13:48 GMT

I think it is but I don't remember the syntax for doing it in Power Query.

Re: Create a measure in query editor

nh27 — Fri, 28 Apr 2023 09:09:00 GMT

Thanks for your help thus far I appreciate it.

Does anyone have any ideas on how to achieve this?