Forum Discussion

telbin's avatar
telbin
New Member
8 years ago

Match records on two variables

Hi, i'm looking for a bit of help please.  I am new to powerbi, and would like to know if the following functionaliy is avaibale.

 

I need to mach two tables, based on employee number and also a varible date, see table below. 

 

ho do i return the expected resuts?

 

thanks in advance.

 

Employee NoStart DateEnd DateArea / Code   Employee NoDateExpected Result
12345601/01/201803/01/2018100   12345603/01/2018100
12456620/02/201820/02/2018500   12345620/02/2018(blank)
453212      12345610/01/2018300
12345604/01/201815/02/2018300   12345612/01/2018300
       45321226/02/2018(blank)
       12456726/02/2018(blank)

4 Replies

  • telbin's avatar
    telbin
    New Member
    8 years ago

    sorry shoud have explained. Table 1 is the first 4 columns, and the second table is Employee No, Date and Expected Results

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      telbin

       

       

      Try this column in Table 2

       

      Column in Table2 =
CALCULATE (
    MIN ( Table1[Area / Code] ),
    FILTER (
        Table1,
        Table1[Employee No] = Table2[Employee No]
            && Table2[Date] >= Table1[Start Date]
            && Table2[Date] <= Table1[End Date]
    )
)
  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago
    Hi, Due to the Employee No in Table 1 has two "123456" number and related two different [Start Date] and [End Date], I could not figure out which date should be compared? Could you please offer me more information about your data logic? Regards, Daniel He
  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi telbin,

    Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

     

    Regards,

    Daniel He