cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alex_0201
Post Partisan
Post Partisan

custom column for related tables

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?

 

4 REPLIES 4
amitchandak
Super User
Super User

@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]) 
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Mariusz
Community Champion
Community Champion

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

 

@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
Community Champion
Community Champion

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

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!