Look up data based on multiple conditions

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.