Lookup function

[Peter_2020]

Hi all, 

I would like to ask you for your help with following situation:

I have database with lot of data about the production orders (TABLE1). Each specific order has info about the project, operation number, reported date and I need to connect somehow these data with following table (TABLE2):

Expected output will looks like this:

It means that I need to define the formula which takes PO, OPERATION, PROJECT, YEAR, REPORTED MONTH from couple of connected tables (this is working now) and compare it with TABLE2 and if it match return VALUE which means column based on the reported month - january = column MONTH1, february = column MONTH2....

Thank you in advance for your help. 

P.

[Wilson_]

Hi Peter,

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.)

[Peter_2020]

Hi Wilson, 

yes sure. You can find pbix file on the link below:

DataExample.pbix

[Wilson_]

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?)

[Peter_2020]

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.

[Wilson_]

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". 🙂

[Peter_2020]

Hi Wilson, 

sorry but I made a mistake in my report and that was the reasons why it doesn't work. Now it is fixed and it works. But still I have one issue: if the dates in table ProductionOrder and Table2 match, everything works correctly, but if they are different, it no longer works.

Is it possilble to set up the formula that if PO is reported 12.1.2024 it will take Value from column 1 wich means January?

Thank you for your help.

P. 

[Wilson_]

Peter,

Sure, if you don't want it to filter by date at all, remove the SelDate variable and the second parameter in the CALCULATE function from the measure.

----------------------------------
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?)

[Peter_2020]

Now the value is calculated for all records, but it make a SUM of the value:

[Wilson_]

Peter,

I'm not exactly following what you're looking for instead of the original solution. I don't know what "Is it possilble to set up the formula that if PO is reported 12.1.2024 it will take Value from column 1 wich means January?" means. Can you use concrete examples to explain the logic?

[Peter_2020]

Hi Wilson, 

yes sure. the logic is very simple:

In table "ProductionData" I have the data about lot fo PO´s with specific numbers. Each PO has specific REPORTED DATE. And from this specific reported date first of all I need take number/name of the month and get the VALUE from the Table2 which will exactly match with the data from ProductionTable (project, operation, year...) AND IT WILL TAKE THE VALUE FROM SPECIFIC COLUMN BASED ON THE NUMBER/NAME OF THE MONTH.

Example: if PO is reported on 12.1.2024, formula has to return the Value from column 1 - which means January. If PO is reported on 19.2.2024, formula has to return the Value from column 2 - which means February.....

But basically this is already working in model which you provide, but only isssue is that from some reasons it make a SUM of the value...

P.

[Wilson_ · Accepted solution]

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?)

[Peter_2020]

Hi Wilson, now it works. Thank you for your help.