Add Column using value form another table

[Anonymous]

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.

[Phil_Seamark]

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])

[Anonymous]

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?

[Phil_Seamark]

Hi @Anonymous

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

[Phil_Seamark]

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

[jimbobobwalker]

Can i use this same method but joining on two columns. I've read where people say I should create a merged column in power query to be able to setup a relationship between two tables in the data model, however the two tables I want to join together are summarized tables from my transaction tables, and joining on my transaction tables makes no sense. What I need is add the "Accepted Debit Entry Count" field from the OB_SUMMARY table to the RER_SUMMARY table as it is the base for multiple calculation i need to create in the RER_SUMMARY table.

For example "Unauthorized Return Rate" = "Unauthorized Return Counts"/"Accepted Debit Entry Count"

Any help would be appreciated

[jimbobobwalker]

Sorry also the join between the two would partner to partner and FedACH ProcessDate to FedACH ProcessDate

[Anonymous]

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]?

[rajatanand]

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

[Phil_Seamark]

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?

[Anonymous]