Forum Discussion

Peter_2020's avatar
Peter_2020
Helper III
2 years ago
Solved

Lookup function

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.

 

 

Need Help
  • Wilson_'s avatar
    Wilson_
    2 years ago

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

12 Replies

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    Hi Peter,

     

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

      • Wilson_'s avatar
        Wilson_
        Memorable Member
        2 years ago

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