cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Help with Lookupvalue

Hi All, 

 

I have a budget table where I have the monthly budget for different product groups and different account types. 

Similarly, I have a sales table where I have daily sales transactions for the same product groups and account types. 

I want to bring in my sales transactions into my budget table so I can plot sales vs. budget by month for different product groups and account types. 

I tried lookupvalue but it gives me the error "A table of multiple values was supplied where a single value was expected". 

 

Any help would be great. Ready to share my data and/or pbix file if needed. 

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Help with Lookupvalue

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

View solution in original post

8 REPLIES 8
Highlighted
Post Patron
Post Patron

Re: Help with Lookupvalue

@kartiklal18  Do you have date table in your dataset? Else you would need to create a date table in your dataset and then you would need to link your budget table and sales transaction table with the date table using date field. This way by selecting a month using date field from date table you would be able to view the budget and actuals at the same time. 

 

You can create a date table using below dax syntex. Go to table data view (second icon on the left side) and then click new table and enter below dax syntex

 

date_table = CALENDARAUTO()

Highlighted
Helper II
Helper II

Re: Help with Lookupvalue

Hi @negi007 , 

 

I have a date table and my budget table and sales table are both linked to the date table. 

 

It works fine when I want to plot sales vs. budget by month but if i want to drill down into product group and/or account type its doesn't return what I want. 

 

Please see screenshot. My budget values are correct but my sales values aren't what I want when I drill down into product group and account type. 

 

Capture.PNG

Highlighted
Post Patron
Post Patron

Re: Help with Lookupvalue

@kartiklal18You would need to link product id of budget and transaction tables. I would suggest you to have a seperate product table with all product ID and product details. Then you would need to link product ID from product table with product ID in budget and transaction table. This would show you the correct output.

Highlighted
Helper II
Helper II

Re: 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. 

 

Capture2.PNG

Highlighted
Post Patron
Post Patron

Re: Help with Lookupvalue

@kartiklal18  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.

Highlighted
Helper II
Helper II

Re: Help with Lookupvalue

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 

Highlighted
Community Champion
Community Champion

Re: Help with Lookupvalue

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

View solution in original post

Highlighted
Helper II
Helper II

Re: Help with Lookupvalue

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!  

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors