Forum Discussion
Help witch fetching values from another table depending on conditions
Hi,
Im embedding pictures of the different tables down below.
Data table:
Supplier table:
Rules table:
What I've done to solve my problem is to create a new column, in both the Data and Rules table, with a CONCATENATE() of "SupplierID" and "Code", because it will always be a unique number in the Rules table. That way I could edit the LOOKUPVALUE() to match those values instead.
Im sure there is a more elegant solution to this that dont add unecessary size to the model.
See new example:
Advocate IHi,
Okay, could you change the first line with LOOKUPVALUE() to:
LOOKUPVALUE(Rules[Code], Rules[Code], 'Datatable'[Code]) = 'Datatable'[Code]This is because your relationship is matching on more suppliers. But there can be only one matching value. In your rules table there are more matching values now. The code above is looking for one matching value in the code column. That is almost the same way you created with the CONCATENATE().
Did you made your relationship between the two tables on the suppliers column or on the code column?
Will you let me know if this works?
The relationship of the tables are setup on SupplierID - with one to many from the supplier table to both other tables:
If I change the code as you mention, it now returns a match even if there is none. (Added a new column for testing "Category2"). There is no rule for "Supplier 3" in the rules table.I really do appreciate you taking your valuable time to help me!
- SomeDataDude
Could you provide a sample .pbix file? Then I could test some things. Because I thought you connected Rules to Data and Suppliers to Data, but I was wrong.
I'm not allowed to upload a file here so try if you get access with this link: https://www.dropbox.com/sh/3lcca8y78bjtzet/AACqz8wG0nGPXXcSrGbu-F9Ma?dl=0
I've added some code to my example, but I worry that all the LOOKUPVALUE() will be very taxing on the system once the real data with maybe 200-300 000 rows are calculated.