Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
Awesome, glad you had a win!
Cheers
Manfred
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
148 | |
116 | |
104 | |
89 | |
65 |