Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
djm7
Frequent Visitor

direct query and import tables

good afternoon

 

I have two tables the large table is a direct query table in ssas

the small table is an import table with a few hundred rows.

I join the two 

but my next step is to use the dax related function to look up a value in the second small table, but I get

the following error

The column ...................... either doesn't exist or doesn't have a relationship to any table available in the current context.  the relationship between the two table is many to one, but I still get the ( ) on the line connecting the two tables and I assume this is because one is a direct query table and the second is an import table.

is this correct, anyway to do the lookup without using the related and without making the first large table into an import table

 

 

5 REPLIES 5
v-tangjie-msft
Community Support
Community Support

Hi @djm7 ,

 

Whether the advice given by danextian and suparnababu8 has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

 

Best Regards,

Neeko Tang

danextian
Super User
Super User

Hi @djm7 

 

Yours is a case of a limited relationship

  • many-to-many
  • from one source group to another (import to direct query and vice versa)
  • danextian_0-1737794535226.png

     

Please see this documentation https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#limited-... 

 

Alternative (or not), these approaches I couldn't use with SQL server when the calculation crosses between two different source groups:

  • LOOKUPVALUE
  • RELATED
  • CALCULATE(....FILTER(...
  • measures that try to propagate a virtual relationship - using TREATAS or table1[column] in values(table2[column)
  •  









Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
suparnababu8
Super User
Super User

Hi @djm7 

 

I think RELATED dax function doesn't work well across these mixed storage modes. It should be used for same storage modes only.  

 

I am recommending you to use LOOKUPVALUE dax, this fuction may work better for mixed storage sceniories.

Please go through this Solved: RELATED Function with a Direct Query Table - Microsoft Fabric Community accepted solution provided by @johnt75 . You will get the better clarity.

 

Thanks!

Tutu_in_YYC
Super User
Super User

Is the look up in a Measure or Calculated Column?

calculated column

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.