Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Lookup target from another table

I am unable to figure out to create a measure so that I can lookup the target for an activity based on the date range.   I have two tables as below. (Even though I am just showing 3 - 4 columns her...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Anonymous,

    In DimTarget, the start and end time including whole month, so you can use month to identify the date. I reproduece your scenario and get expected result.

    Create month column in tblActivity and DimTarget table using the formulas.

    month = MONTH(tblActivity[Date])

month = MONTH(DimTarget[DateStart])


    Then you can use LOOKUP function. You look up DimTarget[target] when tblActivity[Activity] equals DimTarget[Activity], and tblActivity[month] equals DimTarget[month].

    target = LOOKUPVALUE(DimTarget[Target],DimTarget[Activity],tblActivity[Activity],DimTarget[month],tblActivity[month])

     

     

    If you have other issues, don't hesitate to let me know.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Anonymous,

In DimTarget, the start and end time including whole month, so you can use month to identify the date. I reproduece your scenario and get expected result.

Create month column in tblActivity and DimTarget table using the formulas.

month = MONTH(tblActivity[Date])

month = MONTH(DimTarget[DateStart])


Then you can use LOOKUP function. You look up DimTarget[target] when tblActivity[Activity] equals DimTarget[Activity], and tblActivity[month] equals DimTarget[month].

target = LOOKUPVALUE(DimTarget[Target],DimTarget[Activity],tblActivity[Activity],DimTarget[month],tblActivity[month])

 

 

If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia