cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shanka Regular Visitor
Regular Visitor

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 here, I have many and would like to use them as filters)

 

tblActivity is my fact table which contains activies, date and value. Now I want to get the targets for those activies from my DimTarget table.

 

tblActivity
ActivityDateValue
Upsell02-Jan-1710
Cross Sell11-Jan-1712
New addition25-Jan-1714
Upsell03-Feb-1716
Cross Sell20-Feb-1720
New addition27-Feb-1721

 

DimTarget
ActivityDateStartDateEndTarget
Upsell01-Jan-1731-Jan-178
Cross Sell01-Jan-1731-Jan-179
New addition01-Jan-1731-Jan-1712
Upsell01-Feb-1728-Feb-1716
Cross Sell01-Feb-1728-Feb-1718
New addition01-Feb-1728-Feb-1724

 

Desired output..

 

Output 
ActivityDateValueTarget
Upsell02-Jan-17108
Cross Sell11-Jan-17129
New addition25-Jan-171412
Upsell03-Feb-171616
Cross Sell20-Feb-172018
New addition27-Feb-172124

 

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-huizhn-msft Super Contributor
Super Contributor

Re: Lookup target from another table

Hi @shanka,

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

 

1.PNG

 

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

Best Regards,
Angelia

View solution in original post

1 REPLY 1
Highlighted
v-huizhn-msft Super Contributor
Super Contributor

Re: Lookup target from another table

Hi @shanka,

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

 

1.PNG

 

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

Best Regards,
Angelia

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 311 members 3,274 guests
Please welcome our newest community members: