I need to be able to find values in other tables based on conditions from two different tables:
I have the follwing tables:
- Table 1 with information about countries and Order no.
- Table 2 with list of Order no. and groceries
- Table 3 Warehouse 1: with countries, groseries and if it is on stock or not
- Table 4 WH 2: with countries, groseries and if it is on stock or not
- Table 5 WH 3: with countries, groseries and if it is on stock or not
Table 3, 4 and 5 is direct query from another semantic model, hence cannot be appended.
I need to make a table that can show the combination of countri and groceries for an oder (chosen by slicer) and if these are on stock or not in any of the warehouses.
I have a relationsship between table 1 and 2. Where 2 is filtering 1.
How can I fetch the status from the 3 direct query tables?
hello @cmleo
as others mentioned about direct query, so i would do something like below.
create a new table contained of warehouse. This table only works as slicer.
create as many measures as your goods with this following DAX.
Apples =
IF(
SELECTEDVALUE('Warehouse'[Column1])="Warehouse 1",
MAXX(
FILTER(
'Table 3',
'Table 3'[Country]=SELECTEDVALUE('Table 1'[Country])&&
'Table 3'[Grocery]="Apples"
),
'Table 3'[Status]
),
IF(
SELECTEDVALUE('Warehouse'[Column1])="Warehouse 2",
MAXX(
FILTER(
'Table 4',
'Table 4'[Country]=SELECTEDVALUE('Table 1'[Country])&&
'Table 4'[Grocery]="Apples"
),
'Table 4'[Status]
),
IF(
SELECTEDVALUE('Warehouse'[Column1])="Warehouse 3",
MAXX(
FILTER(
'Table 5',
'Table 5'[Country]=SELECTEDVALUE('Table 1'[Country])&&
'Table 5'[Grocery]="Apples"
),
'Table 5'[Status]
)
)))
Just change value 'Table 3'[Grocery], 'Table 4'[Grocery], and 'Table 5'[Grocery] for the other goods.
put all the measures into table visual
A little bit of works but as long as the result is achived.
the other thing, as you mentioned, you can not see the table value in direct query.
if you want to create a calculated table from a direct query table, i think you can summarize the direct query table.
You can see the value of direct query table in summarize table from direct query table. Last time i succeed by doing this.
Summarize all column in direct query table so you can prevent any value is missing because getting summarized.
Hope this will help.
Thank you.
Hi @cmleo ,
You cannot append Table3, Table4, Table5 because they are Direct Query from semantic models. Also, there's no relationship between them and the rest of the model.
Tables 3/4/5 are in DirectQuery mode and from separate semantic models, you cannot create relationships between them and your other (import) tables.
This means no natural filter context flows from your selection of country or grocery.
Any attempt to use visuals or relationships alone will not fetch data from those warehouse tables correctly.
Combine the data from Table1 and Table2 based on Order no, Look up stock status from each of the warehouse tables (Table3, Table4, Table5).Show "On stock" if any warehouse has the item in stock.
Create a new table to show all Country-Grocery combinations for the selected Order no:
CountryGroceryMatrix =
VAR SelectedOrderNo = SELECTEDVALUE('Table1'[Order no])
RETURN
FILTER (CROSSJOIN (
VALUES('Table1'[Country]),
VALUES('Table2'[Grocery])),
RELATED('Table1'[Order no]) = SelectedOrderNo &&
RELATED('Table2'[Order no]) = SelectedOrderNo)
Now create a DAX measure to determine if the item is on stock in any warehouse:
Stock Status =
VAR _country = SELECTEDVALUE('CountryGroceryMatrix'[Country])
VAR _grocery = SELECTEDVALUE('CountryGroceryMatrix'[Grocery])
VAR WH1 = TRIM(LOWER(CALCULATE(
MAX('Table3'[Status]),
FILTER('Table3', 'Table3'[Country] = _country && 'Table3'[Grocery] = _grocery)
)))
VAR WH2 = TRIM(LOWER(CALCULATE(
MAX('Table4'[Status]),
FILTER('Table4', 'Table4'[Country] = _country && 'Table4'[Grocery] = _grocery)
)))
VAR WH3 = TRIM(LOWER(CALCULATE(
MAX('Table5'[Status]),
FILTER('Table5', 'Table5'[Country] = _country && 'Table5'[Grocery] = _grocery)
)))
VAR StatusList = { WH1, WH2, WH3 }
RETURN
IF (
"not on stock" IN StatusList,
"Not on Stock",
IF (
"on stock" IN StatusList,
"On Stock",
"Null"
))
Hope this helps,
Thank you.
My datamodel looks like this:
I have tried various ways of relatioships, but it does not show the desired output.
Helpful resources
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
