Forum Discussion
Help with Lookupvalue
Hi negi007 ,
Unfortunately my budget table doesn't have a product Id.
This is what my budget table looks like. I need to link the year, month, account type and product group in my budget table to the year, month, account type and product group in my sales table.
Anonymous if you link product group in budget and transaction table it may result in many-2-many relationship which may not be good for your dataset. Try if can create some unique key using multiple columns in your budget table and transaction table. That may help in creating one-2-one relationship between two tables. If you are not able to resolve resolve your issue, will it be possible for you to share your pbix file.
Hi negi007 ,
Please see link for pbix file.
My Sales Table is called "Chems Data" and my budget table is called "Budget by Account Type".
The account type column is called "QED" in both these tables. I need to link the columns year, month, QED and Product Group.
Thanks for your help!
https://www.dropbox.com/s/2x26e8g7jotfgv7/LENNOX%20-%20Management%20Report.pbix?dl=0
I looked at your pbix file. I don't have access to the data to modify the queries (as I shouldn't), so can't provide exact M code. But you basically want to add a custom column in the query editor on both of those table with the Concatenation of those four columns. Note that if any are not text data type you'll need to wrap that part in Text.From( ). For example -
=Text.From([Year])&Text.From([Month])&[QED]&[Product Group]
You can then make a relationship between them (hopefully a 1:many).
Two unsolicited suggestions for your data model - it looks like you might be able to append a number of tables (e.g., the DailyBudget tables all appear to have same columns in them). Also, you have a number of bi-directional relationships which may lead to confusion/frustration/errors when writing more advanced DAX expressions. Try to avoid having those. The ironic thing is one has to learn more advanced M/Query techniques to simplify a model to make the DAX easier to write sometimes. Simple Model, Simple DAX.
I hope you don't mind the suggestions. I am speaking from the experience of doing the same things (and worse).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat ,
Thank you! Your solution works as intended.
I'm aware that I need to clean up my data model so thank you for the suggestions. It is really appreciated!
