Create a Calculated Column from Different Tables

[Anonymous]

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]

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 : 

https://community.powerbi.com/t5/Desktop/Direct-query-and-calculated-column-based-on-two-tables/m-p/130143/highlight/true#M55425

Hope that helps

Cheers

Manfred

[Anonymous · Accepted solution]

@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

[MarksmanWaugh79]

I know it has been some time, but how did Merge solve the problem for you? You can't merge in Direct Query. I can only think you converted to Import Mode... something I cannot do in the project I am working on. 

[mwimberger]

Awesome, glad you had a win! 

Cheers

Manfred