cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GCGradwell
Helper I
Helper I

Create a Calculated Column from Different Tables

Hi ,

 

I can’t work out how to create a calculated column using 2 columns from different tables in the formula.

 

I’m trying to create a calculated column ‘Z’ using a simple formula: X – Y

 

Column X is in table 1

Column Y is in table 2

 

When creating Z in table 1, it does not see columns from any other tables. There is a relationship between both tables which I have checked is still there.

 

This is very strange, I have created calculated columns using column data from 2 separate tables many times before and never had any problems.

 

Any ideas?

 

 

Thanks,

 

Greg

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft

@GCGradwell

 

Based on my understanding, it depends on the relationship.

 

If 1:1, below formula should work.

calculated column =
table1[X] - RELATED ( table2[Y] )

If 1:many, the following formula should work.

calculated column =
oneTable[X]
    - CALCULATE ( SUM ( manyTable[Y] ), ALLEXCEPT ( manyTable, Table10[linkedCol] ) )

View solution in original post

13 REPLIES 13
Eric_Zhang
Microsoft
Microsoft

@GCGradwell

 

Based on my understanding, it depends on the relationship.

 

If 1:1, below formula should work.

calculated column =
table1[X] - RELATED ( table2[Y] )

If 1:many, the following formula should work.

calculated column =
oneTable[X]
    - CALCULATE ( SUM ( manyTable[Y] ), ALLEXCEPT ( manyTable, Table10[linkedCol] ) )

We have two tables with a relationship of many : 1 between Table1and Table2.
We are getting errors for below derive column for filter condition : Table1[Web Activity Date] <= RELATED(Table2[Lead Created Date])

------------------
# Distinct Forms Filled by Net New Lead = CALCULATE(DISTINCTCOUNT(Table1[Form ID]),v_rpt_omniture[Post Visid High Low] <> "10"
&& Table1[Form Completions] = "1"
&& Table1hit Source] = "1"
&& Table1[Exclude Hit] = "0"
&& Table1[Web Activity Date] <= RELATED(Table2d[Lead Created Date]))

Hi,

In Table1, write the RELATED function as a calculated column formula and give a title to that column as Lead date.  Write another calculated column column called test with this formula =Table1[Web Activity Date]<=Table1[Lead date] .  Revise your measure to:

# Distinct Forms Filled by Net New Lead = CALCULATE(DISTINCTCOUNT(Table1[Form ID]),v_rpt_omniture[Post Visid High Low] <> "10"
&& Table1[Form Completions] = "1"
&& Table1hit Source] = "1"
&& Table1[Exclude Hit] = "0"
&& Table1[Test] = TRUE())


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you @Ashish_Mathur for the solution. It is helpful.

Just want to confirm : Are we saying we cannot use related table with multiple filter condition in CALCULATE function?

You are welcome.  I prefer using a calculated column formula because it simplifies the measure and allows me to go back to the table and apply filters, if i ever have to.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

What if it's a Many to many relationship? 

I'm trying to do a similar thing but can't get the formula you provided to work.

 

I have two tables and want to subtract Column A from Table 1 from Column B from Table 2.  There is a relationship between the two tables via a common ID. 

 

 

The relationship is one to many whereby the value being subtracted is the 'one table' and the one from which data is being subtracted from, is the 'many table'.

 

The data is in date format.

 

i.e 05/03/2017 - 03/03/2017 = 2

 

Any help appreciated.

 

Thanks

Hi ,

 

i am trying to create a calculated column using 2 different tables. i am getting error.

 

My query:

 

=if(table1[column1] = "abc"  || table1[column1]  =  "bcd", "Others",  if( table1[column1]  ="xyz" ,  if( table2[column2]  = "pqr", "Oman", if( table2[column2] = "que", "hyz", "ecc"))))

 

Error:

 

column 'column2' in 'table2' cannot be found or may not be used in this expression.

 

please suggest proper syntax.

 

thank you.

 

 

 

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

I am new to Power BI .  Can I use data in tables to calculate more data?

 

My example is: I have Forecasted Product sales for 2 weeks. Prod 1, 100 units, Prod 2, 200 units.

 

I have a table with the Sales Mix % by Week, Week 1, 75%, week 2, 25%

 

Can I calculate out these to two table to create a new table with forecasted sales by week.

eg

Week1, Prod1, 75 units

Week2, Prod1, 25 units

Week1, Prod2, 150 units

Week2, Prod2, 50 units

 

I am calculating this in excel and then loading to power BI. 

 

Thanks

Sam

Hi @IanSamTaylor,

 

See the Data table in this file.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
CahabaData
Memorable Member
Memorable Member

as someone coming to Power BI from databases - my immediate question of your post is - where is the unique ID (key) field for these two tables?  In order to any math one must know which row's value to use on both sides......

 

Perhaps that's a given - just thought I would throw that in....   in which case your expression would be involving Related Table....

 

 

 

 

 

 

www.CahabaData.com

You are asking very general questions, so it is hard to know what you are trying to do, or why.  My experience is that most calculated columns that new users try to build are completetly un-necessary and probably worse still - it normally is the wrong approach.  Read my article here, then see if you can do what you want with measures.

 

http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors