Forum Discussion
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 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?
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.
11 Replies
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
Sure. I hust wasn't aware of how to paste in the data correctly.
Table 1
Country Order no United States 1 Canada 2 Mexico 3 Brazil 4 Argentina 1 United Kingdom 2 Germany 3 France 4 Italy 1 Spain 2 China 3 Japan 4 South Korea 1 India 2 Australia 3 New Zealand 4 South Africa 1 Egypt 2 Russia 3 Saudi Arabia 4 Table 2
Grocery Order no Apples 1 Bananas 1 Carrots 1 Potatoes 1 Tomatoes 2 Onions 2 Milk 2 Eggs 2 Cheese 3 Bread 3 Rice 3 Pasta 4 Chicken 4 Beef 4 Fish 4 Butter 5 Yogurt 5 Cereal 5 Flour 6 Sugar 6 Table 3
Country Grocery Status United States Apples On stock Canada Bananas Not on stock Mexico Carrots Not on stock Brazil Potatoes On stock Argentina Tomatoes Not on stock United Kingdom Onions On stock Germany Milk On stock France Eggs On stock Italy Cheese On stock Spain Bread On stock China Rice On stock Japan Pasta On stock South Korea Chicken On stock India Beef Not on stock Australia Fish Not on stock New Zealand Butter Not on stock South Africa Yogurt Not on stock Egypt Cereal Not on stock Russia Flour On stock Saudi Arabia Sugar On stock Table 4
Country Grocery Status United States Apples Not on stock Canada Bananas On stock Mexico Carrots On stock Brazil Potatoes On stock Argentina Tomatoes On stock United Kingdom Onions On stock Germany Milk On stock France Eggs Not on stock Italy Cheese Not on stock Spain Bread Not on stock China Rice Not on stock Japan Pasta Not on stock South Korea Chicken Not on stock India Beef On stock Australia Fish On stock New Zealand Butter On stock South Africa Yogurt On stock Egypt Cereal On stock Russia Flour Not on stock Saudi Arabia Sugar Not on stock Table 5:
Country Grocery Status United States Apples On stock Canada Bananas On stock Mexico Carrots On stock Brazil Potatoes On stock Argentina Tomatoes On stock United Kingdom Onions On stock Germany Milk On stock France Eggs On stock Italy Cheese On stock Spain Bread Not on stock China Rice Not on stock Japan Pasta Not on stock South Korea Chicken Not on stock India Beef Not on stock Australia Fish Not on stock New Zealand Butter Not on stock South Africa Yogurt Not on stock Egypt Cereal On stock Russia Flour Not on stock Saudi Arabia Sugar Not on stock My datamodel looks like this:
I have tried various ways of relatioships, but it does not show the desired output.
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.Hi cmleo ,
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
Best Regards,
Chaithra E.Hi.
Thank you so much for looking into my issue.
I acutally managed to solve it in a bit different way than suggested here, but I still have an issue.
I now have a calculated table that refers to remote table. This results in refresh faillure when published. How can I get about that?I combined the columns in a new table this way:
WH status table = UNION(SELECTCOLUMNS('Warehouse 3 table', "Groseries", 'Warehouse 3 table'[Groseries], "Country", 'Warehouse 3 table'[Country], "Status", 'Warehouse 3 table'[Status]),
SELECTCOLUMNS('Warehouse 4 table', "Groseries", 'Warehouse 4 table'[Groseries], "Country", 'Warehouse 4 table'[Country], "Status", 'Warehouse 4 table'[Status]),
SELECTCOLUMNS('Warehouse 5 table', "Groseries", 'Warehouse 5 table'[Groseries], "Country", 'Warehouse 5 table'[Country], "Status", 'Warehouse 5 table'[Status]))
From there I just made a simple relationship to Table 2. With Groceries in Table 2 filtering Groceries in the new WH Status table.Hi cmleo ,
In DirectQuery mode, calculated tables are not supported because they require in-memory processing, which isn't compatible with the real-time querying approach of DirectQuery. Instead of loading the full data model into memory, DirectQuery sends queries directly to the underlying data source only when required by visuals.
Alternatively, Use DAX Measures for dynamic calculations, consider using measures instead of calculated tables. Measures compute results based on the current report context without storing additional data. Transform Data in Power Query if you need to filter or reshape data from remote tables, use Power Query to perform these transformations before loading the data into your report.
Create Views in the Source System, if complex transformations are needed, consider building a view in the underlying data source to encapsulate the logic. You can then connect Power BI directly to this view.
Make sure you have the necessary permissions to access the shared semantic model and any underlying data sources. Check Data Source Settings: In the Power BI Service, ensure your data source settings and credentials are set up correctly for the shared semantic model.
Hope this helps,
Chaithra E.Hi
I really appreciate your input.
My direct query tables comes from other Power BI semantic models. They are therefore, not visible in power wuery, and I cannot have calculated tabels. So I am limited to DAX measures, which I untill now hasn't had any success with.
So if you have good ideas to how I can convert my calculated column into a measure, I will be thrilled. 🙂
