Forum Discussion

ksummers68's avatar
ksummers68
Regular Visitor
8 years ago
Solved

Add lookup data from another table/column using custom columns

I have a Pay Run ID table

 

Pay Run ID (Year/Pay Period)Pay Begin DatePay Period End Date
5012004-12-202005-01-02
5022005-01-032005-01-16
5032005-01-172005-01-30

 

I have a requisition table with Created Date

 

Requisition #Req. TitleDate Created
27629Test A1/30/2005
27629Test A1/30/2005
27630Test C12/22/2004

 

I want to add a column to the requisition table and display the Payrun ID

 

Requisition #Req. TitleDate CreatedPayrun ID
27629Test A1/30/2005503
27629Test A1/30/2005503
27630Test C12/22/2004501
Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    your output seems to be wrong, 503 will never get return because pay run doesn't have that date range.

     

    Here is the solution thou, add new measure in your requistion table:

     

    PayId = 
var r = MAX(Req[Date Created]) 
return Calculate(Max(PayRun[Pay Run ID (Year/Pay Period)]), Filter(
All(PayRun), 
r >= PayRun[Pay Begin Date] && r <= PayRun[Pay Period End Date]
)
)
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi ksummers68,

     

    parry2k's solution works great in a measure. If you would like to add a column in source table, you just need to make a little modification:

    PayId =
CALCULATE (
    MAX ( 'Pay Run ID'[Pay Run ID (Year/Pay Period)] ),
    FILTER (
        ALL ( 'Pay Run ID' ),
        requisition[Date Created] >= 'Pay Run ID'[Pay Begin Date]
            && requisition[Date Created] <= 'Pay Run ID'[Pay Period End Date]
    )
)

    Best regards,

    Yuliana Gu

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    your output seems to be wrong, 503 will never get return because pay run doesn't have that date range.

     

    Here is the solution thou, add new measure in your requistion table:

     

    PayId = 
var r = MAX(Req[Date Created]) 
return Calculate(Max(PayRun[Pay Run ID (Year/Pay Period)]), Filter(
All(PayRun), 
r >= PayRun[Pay Begin Date] && r <= PayRun[Pay Period End Date]
)
)
    • ksummers68's avatar
      ksummers68
      Regular Visitor
      8 years ago

      You are correct I had the wrong year (I corrected it in the example) I will try this, thank you very much!

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi ksummers68,

         

        parry2k's solution works great in a measure. If you would like to add a column in source table, you just need to make a little modification:

        PayId =
CALCULATE (
    MAX ( 'Pay Run ID'[Pay Run ID (Year/Pay Period)] ),
    FILTER (
        ALL ( 'Pay Run ID' ),
        requisition[Date Created] >= 'Pay Run ID'[Pay Begin Date]
            && requisition[Date Created] <= 'Pay Run ID'[Pay Period End Date]
    )
)

        Best regards,

        Yuliana Gu