Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Show value from other table outside date range

Hi

 

I'm trying to create a DAX measure / calculated column that get the value from table 1  if dates are within slicer visualization witin Power BI (relative dates) date range.

 

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. 

 

Example Firewatcher has got value=3 (2+1) for week 45 in table1, but is not showing in fig1 under ∑ Value in Fig1. since Firewatcher is not valid for this time period.

 

Personal Category in fig1 shall refer to table 1.

 

MERGE = Category +D + Weekno + Weekday

 

I have tried lookupvalue from table 2; POB = LOOKUPVALUE(TABLE1 [VALUE];TABLE1[MERGE];TABLE2[D/N])

 

Not getting the value since Weekno is not corresponding to table 1.

 

I`m appreciate your assistance.

 

Rgds Henrik

 

 

 

Fig1.:

 

 

Table1

 

 

Table 2

 

 

Need Help

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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