Forum Discussion

cmleo's avatar
cmleo
Regular Visitor
1 year ago
Solved

Look up data based on multiple conditions

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 w...
  • cmleo's avatar
    cmleo
    11 months ago

    Hi. 
    Thank you for your advise. 

    First I am reaching out to the once who manage the Power BI semantic model, and asking them to append the tables.

    Hopefully they will do it, as it will be very time consuming and maintenance heavy to make measures for each grocery.

     

    But thank you a lot.

v-echaithra's avatar
v-echaithra
Community Support
1 year ago

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.