Direct query - add values from other table to the main table with conditions

[Kopec]

Hi,

I have created model in "direct query" mode, I dont have experience with it. (I normaly use add Column with combination formulas Calculate + Allexept... now it doesnt work)

I need to add values from one column in "table B" to the "table A" for Column xID.
I have this conditions:

• max values from "Column Order" -1 for each Column xID determine value in Column Personal ID in Table A, which is key for connect to Table B.
• The unique key for this situation in Table A is combination Column xID with Column Personal ID (because values from Personal xID can be repeated).
• I need to add value from Table B - Column Name to the new Column in Table A (in need can by measure), duplicated for same Column xID values.
• (Table A has over 23 milions rows)

[FarhanJeelani]

Hi @Kopec ,

Since you are working in DirectQuery mode, you cannot use CALCULATE + ALLEXCEPT as in Import mode. Instead, you need a DAX approach that works with DirectQuery constraints.

Please try below steps:
1. Identify the Maximum Column Order - 1 for Each xID
You need to determine the (Max(Column Order) - 1) for each xID, which will help you find the corresponding Personal ID.

Create a calculated table (if allowed) or use a variable in a measure:

MaxOrderMinus1 =
VAR MaxOrderTable =
SUMMARIZE ( 'Table A', 'Table A'[xID], "MaxOrder", MAX ( 'Table A'[Column Order] ) - 1 )
RETURN
MaxOrderTable

This will return the maximum Column Order - 1 for each xID.

2. Get the Corresponding Personal ID
Now, filter Table A to retrieve the Personal ID that matches the MaxOrderMinus1 value:

PersonalIDForMaxOrder =
VAR MaxOrder =
LOOKUPVALUE ( MaxOrderMinus1[MaxOrder], MaxOrderMinus1[xID], 'Table A'[xID] )
RETURN
LOOKUPVALUE ( 'Table A'[Personal ID], 'Table A'[xID], 'Table A'[xID], 'Table A'[Column Order], MaxOrder )

This retrieves the Personal ID corresponding to (Max(Column Order) - 1).

3. Lookup the Column Name from Table B
Once you have the Personal ID, use it to fetch the corresponding Column Name from Table B:

ColumnNameFromTableB =
LOOKUPVALUE ( 'Table B'[Column Name], 'Table B'[Column ID], [PersonalIDForMaxOrder] )

Performance Considerations:
Since Table A has 23M+ rows, calculations in DirectQuery will be slow. Some optimizations:

Push filtering to the database (avoid calculated columns if possible).
Use aggregations (precompute values in the database).
Use composite models (import only the required fields, use DirectQuery for large data).
Optimize relationships (ensure indexing on xID and Personal ID in the source database).

Please mark this post as solution if it helps you. Appreciate Kudos.

[Kopec]

Hi Farhan,

thank you for solution.
I cant use the first step as a table (limit 1 milions rows, for this). Can you show me how to compose to the measure? (I had got a scalar mistakes alert).