custom column for related tables

[Alex_0201]

Hi everyone.

I am struggling to understand why I get this error when I try to add a custom column with a simple formula that reads values from a related table:

We cannot apply operator + to types Number and List.
Details:
Operator=+
Left=14492.78215
Right=[List]

There are two related tables (many to many relationship). A table called Daily contains id and md columns. Another table called Datum contains id and elevation columns.

I want to add a custom column in the table Daily with this formula:

=[md]+Datum[elevation]

Each id record has only one elevation in the table Datum.

However, in the Daily table there are several records with the same id and md.

My understading was that each md has and id in the column Daily, this id has only one eleveation in the table Datum. Am I wrong?

[amitchandak]

@Alex_0201 , not very clear. You can transfer data from one table to another

Item Name = RELATED('item'[Brand])
City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City]) // error Month Name = LOOKUPVALUE('date'[Month Year],'date'[Date],Sales[Sales Date]) 

[Mariusz]

Hi @Alex_0201 

Sorry but I'm struggling to understand your requirement, can you specify if you need a custom column in power query or a column in the model using DAX expression.

If you are using power query then you can Merge queries, join Datum into Daily and later expand the elevation column.

If you are trying to do in the model then you can use CALCULATE( SELECTEDVALUE( Datum[elevation] ) ), normally you can use RELATED or RELATEDTABLE but not sure if this can work with many to many.

Personally I would do it in Power Query.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Alex_0201]

@Mariusz the proble is when I merge datum into daily I get some new rows with null values, which is not allowed for many to many relationships. There are about 30 rows in each of the tables.

[Mariusz]

Hi @Alex_0201 

Can you create a share a date sample for both tables?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn