Direct Query Lookup

[Sudhakar510]

Hi I have two direct query's imported into PBI all good.

I am trying to retrieve a Value field from Table 1 to Table 2 using Common ID field column in both tables.

I am applying Lookupvalue but it is not allowing me to do. Please any help on this.

Or any other alternate.

Last option would be tweaking second query to reteive the value but it is very slow to execute.

Could you please help?

[v-yalanwu-msft · Accepted solution]

Hi， @Sudhakar510 ；

I did a simple test. If the ID is unique among the three tables, then a one-to-one relationship can be created.

The final output is shown below:

If you don't want to create a relationship , you could create a measure such as:

Measure = CALCULATE(MAX('student'[StudentID]),FILTER('student',[FamilyID]=MAX('family'[FamilyID])))

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sudhakar510]

Thank you for the response. I managed to tweak the Query in SQL and then brought direct query so that I can get the column.

I have 3 direct query's in same report but all reports will behave like independantly and not interactive to each other. reason for creating 3 querys because one single query is taking time so split into 3 different querys.

However, all three have common ID's with Unique and no duplicate. Can I still relate each other because it is not allowing me to relate each other.

[v-yalanwu-msft]

Hi, @Sudhakar510 ;

As the official document said, 

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

If two tables have relationship, you could try to use RELATED() function.such as:

Measure = SUMX(FILTER('Table',RELATED('Table (2)'[name])<>BLANK()),[amount])

If it is not suitable, you could also use IN() or "=".

https://stackoverflow.com/questions/61342755/dax-formula-compatibility-in-directquery-powerbi-match-...

Or would you mind share simple file or screenshot about your problems?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Sudhakar510  can you try this  as a calculated column if Table A and Table B has relationship.

CALCULATE (
    MAXX ( 'Table B', 'Table B'[Value] ),
    'Table A'[ID] IN VALUES ( 'Table B'[ID] )
)