cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Add Column using value form another table

Hi,

 

I have 2 tables:

 

Table [A]

TimeZone

 

How do I add another colum in [A] that will use a column a vlaue in the TimeZone table without using a Merge Query?

 

The Timezone table only contains one value which is the number of hours to offset. Ideally I would like to use this value as a parameter but have not figured out how to do it.

 

table column.png

 

 

8 REPLIES 8
Highlighted
Microsoft
Microsoft

Re: Add Column using value form another table

Hi @Anonymous

 

If you have a 1 to many relationship between Timezone (1) and Table [A] (many)

 

You can add a calcualted column on Table [A] using syntax like.  Just replace the Value with your own column.

 

New Column = RELATED(TimeZone[Value])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Anonymous
Not applicable

Re: Add Column using value form another table

Hi Phil,

 

Currently both tables are independant and is NO RELATIONSHIP between them.

 

Therefore I cannot use the RELATED DAX function.

 

Do I have to create a relationship?

Highlighted
Microsoft
Microsoft

Re: Add Column using value form another table

Hi @Anonymous

 

It will be easier if you can create a relationship.  Is there any reason why you can't?

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Microsoft
Microsoft

Re: Add Column using value form another table

But here is an alternative that doesn't use relationships.  My TableA has an [ID] column which I use to lookup an [ID] column in the TimeZone table

 

New Column = 
var JoinCol = 'TableA'[ID]
var NewCol = CALCULATE(MAX('TimeZone'[Value]),'TimeZone'[ID]=JoinCol)
return NewCol

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Anonymous
Not applicable

Re: Add Column using value form another table

Hi Phil,

 

Thanks for the rpsonses.

 

My table TimeZone only has one value - 10.

I don't see creating a relationship for this table is the best solution and it seems quite silly to do so.

Why can't I use this value as a parameter and then refer to it when I add my new column in Table [A]?

Highlighted
Microsoft
Microsoft

Re: Add Column using value form another table

Hi @Anonymous

 

I think you can refer to it.  Although it's a bit hard to suggest formulas without knowing a little more about your table structure.

 

Are you able to post a small sample set of data to show what your two tables look like?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Anonymous
Not applicable

Re: Add Column using value form another table

table1.pngtable column.png

Highlighted
Regular Visitor

Re: Add Column using value form another table

If your table has only one value then save it as a measure then you can use that measure in other calculations.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors