Forum Discussion
Create a Calculated Column from Different Tables
I am using DirectQuery storage mode. I have a table where I want to create a new column using two columns already present in my table. However, those two columns come from different tables.
Using DIVIDE('table1'[col1], 'table2'[col2]) I am always thrown an error like "A single value for column 'col1' in table 'table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." Each table has 8 rows and I filter so that I am really only trying to calculate a column of 4 rows. Specifying SUM returns the errors "Function 'SUM' is not allowed as part of calculated column DAX expressions on DirectQuery models."
I am guessing I need to create a distinct key for each table (say 1-8) and create a new relationship between the two tables I am using? I'm not sure what else to try.
mwimberger Thanks! I'm not actually creating a new table but I think creating a unique key in my query for each table and creating those relationships beforehand helped me merge the queries into one table so I could access the columns I wanted.
I found this article really helpful: https://community.powerbi.com/t5/Desktop/merge-queries/td-p/463371
4 Replies
Hi Anonymous
You could try the RELATED function in your table - which will require a relationship between the two tables. DirectQuery will be the stumbling block here I suspect. DirectQuery may not be able to allow you to add columns .
Or if you need a result from the two columns instead consider using two measures
[Measure1] = sum(table1[Col1])
[Measure2] =sum(table2[Col2])
[Measure3] = divide([Measure1],[Measure2])
Not sure if that helps any?
Phil Seamark posted a solution here that may help by creating a new table :
Hope that helps
Cheers
Manfred
mwimberger Thanks! I'm not actually creating a new table but I think creating a unique key in my query for each table and creating those relationships beforehand helped me merge the queries into one table so I could access the columns I wanted.
I found this article really helpful: https://community.powerbi.com/t5/Desktop/merge-queries/td-p/463371
Awesome, glad you had a win!
Cheers
Manfred
