Forum Discussion
Show value from other table outside date range
Microsoft EmployeeHi Anonymous,
This works ok if category is within the date range in table 2, however if this is not the case, "Value" from Table1 is not showing for the given time period.
What date range should be considered in Table2?
Not getting the value since Weekno is not corresponding to table 1.
What do you mean "Weekno is not corresponding"?
As there existing too much data fields in above table, would you please provide some dummy data that can illustrate your scenario more clearly? And show us the desired result returned by LookUp.
Regards,
Yuliana Gu
Hi Yuliana
Thanks for your response.
I would like to return values based on slicer value.
Start of week in Table 2/1 is linked to Datekey in Calendar query.
When I say "date slicer" I am referring to the visualization:
I am trying to lookup the personnel Category and return the "no of personnel" in Table 1. Personnel Category “CAT-1” should show 3 for every day as shown below --> see green value.
The formula I thought would work to meet the goals is following (in Table2):
VALUE = LOOKUPVALUE(TABLE1 [No of personnel];Table1[Merge];Table2[Merge])
No of personnel = 2 for Day shift and 1 for night shift for CAT-1 every day in week45.
No of personnell = 1 for Dayshift for CAT-2 every day in week45.
Table 2:
Merge = Personnel Category +Day/Night Shift +Weekno + Weekday
However, since CAT-1 in Table2 is not valid for Weekno 45 (only 46), no “no of personnel” is showing in the result since date slicer = Week 45.
CAT-1 should give 3 for each weekday as specified in Table1. Personnel Categories in Result are based on Table 1 categories.
Hope it is more clearer. Thanks.
Pls ask if you need more info.
Regards
Henrik