Forum Discussion
Lookup function
Peter,
Try this instead (instead of matching on the exact date, it matches on the month):
VALUE = VAR SelOperation = SELECTEDVALUE ( ProductionData[OPERATION] ) VAR SelMonth = MONTH ( SELECTEDVALUE ( ProductionData[REPORTED DATE] ) ) VAR Result = CALCULATE ( SUM ( Table2[Value] ), Table2[OPERATION] = SelOperation, MONTH ( Table2[Table Date] ) = SelMonth ) RETURN Result
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Hi Peter,
Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.)
Hey Peter,
Here's my updated file. I have the table below in my pbix.
However, I made a few very important updates to your data model to produce this result:
- Unpivoted the MONTH1, MONTH2, MONTH3 columns in Table2 (and converted the year and month to a date). It is a best practice to store the data in individual rows instead of in their own column, for multiple reasons. Two related reasons are because it's much more efficient for your fact tables to have more rows than to have more columns and because computation for calculations like you want are simpler to do.
- Merged the Projects and Table2 tables together. There is no reason to have the MATNR stored in a separate table, instead of as an additional column in Table2.
- Changed the relationship between the Calendar and ProductionData to be a one-to-many, single direction relationship. It's a best practice to avoid bi-directional relationships as much as possible.
- Added a relationship between ProductionData and Table2.
There is further data model optimization available but I did the minimum necessary to answer your question. If you will be working with data and data models in your career, I strongly suggest you go through this free course from SQL on data modeling basics. It helped me tremendously and I hope it can do the same for you! 🙂
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)Hi Wilson,
thank you for updated file and explanation of the changes you made. I tried to copy the same solution to my dataset. It works but measure "VALUE" give s me high values, example - expected value is 3 and I have 878.
I think that problem will be in this part of the formula:
Because if you have only one specific record with specific Project, Operation, Date it works well, but if you have more than one record it gives back the sum of these values what is wrong. I tried to replace the sum with other command but without the success.
Any idea how to fix it?
Thank you
P.
Peter,
What expression is correct instead of the highlighted obviously depends on how you want to aggregate the values. Can you provide an example of multiple records for a specific Project, Operation and Date and what your expected outcome is? I can't help further if you just say "the sum of these values is wrong". 🙂