Forum Discussion
Unable to use relate and lookup function on two different data sets (direct query + import)
I have two different data sources
1. postgre sql (live connection using direct query) has todays data.
2. amazon redshift (updates once daily using import) has previous 90days data.
A model of these two queries is created with many to many relationship.
Both the data sets have same columns, i want to import all the columns from postgre query to redshift query or vice versa using RELATED() or LOOKUP() function but its not letting me to do so and throwing below errors.
Need assistance on this.
You are getting errors because Power BI does not allow using RELATED() or LOOKUPVALUE() functions between tables that use different storage modes—one DirectQuery (PostgreSQL) and one Import (Redshift).
Why this happens
DirectQuery and Import tables cannot be mixed for DAX relationship functions.
Calculated columns and some DAX functions like RELATED() only work when both tables use the same storage mode.
What you can do
Change both tables to use Import mode if possible.
Or, if you must use DirectQuery for PostgreSQL, you need to keep your analysis separate or use Power BI Composite models ("mixed mode") features, but you’ll be limited in what calculated columns and DAX you can use.
Alternatively, merge the tables outside Power BI (for example, in your ETL process or in a database view) so that both are in the same storage mode before bringing them into Power BI.
Let your admin know if you need to switch storage modes, as this is the main limitation.
5 Replies
- Pragati11
Super User
Hi SameshSawant
DirectQuery connection son't support calculated columns.
You are trying to create calculated columns using RELATED or LOOKUP in Power BI which is not allowed in your scenario of live connection.
If you still want to create these calculated columns, you have to switch to IMPORT mode in Power BI. Then you will be able to create calculated columns using these DAX functions.
Shubham_rai955You are getting errors because Power BI does not allow using RELATED() or LOOKUPVALUE() functions between tables that use different storage modes—one DirectQuery (PostgreSQL) and one Import (Redshift).
Why this happens
DirectQuery and Import tables cannot be mixed for DAX relationship functions.
Calculated columns and some DAX functions like RELATED() only work when both tables use the same storage mode.
What you can do
Change both tables to use Import mode if possible.
Or, if you must use DirectQuery for PostgreSQL, you need to keep your analysis separate or use Power BI Composite models ("mixed mode") features, but you’ll be limited in what calculated columns and DAX you can use.
Alternatively, merge the tables outside Power BI (for example, in your ETL process or in a database view) so that both are in the same storage mode before bringing them into Power BI.
Let your admin know if you need to switch storage modes, as this is the main limitation.
- Pragati11
HI Shubham_rai955
Thanks for validating my response.
Hi SameshSawant ,
Thank you for reaching out to the Microsoft fabric community forum.
Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by Pragati11 and Shubham_rai955 . If you still require support, please let us know, we are happy to assist you.
Thank you.
Hi SameshSawant ,
We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.Thank you.
