Forum Discussion

RichWyeth's avatar
RichWyeth
Frequent Visitor
9 years ago
Solved

Lookup Values and Measures

Hi,

 

I have a table that holds UK Bank Holiday Dates and I have used a Lookup value to pull the correct number of days into another table. I use this against a weekday calc to subtract the bank hoilidays and give an actual working days per month.

 

The problem I am having is that when I want to use this actual working day calc in a measure, the lookup value doesn't work.

It fails to see the 3rd result (Search Value 1).

 

I have linked my two tables via date and checked the formatting is the same and tried a few different ways to obtain the number of bank holidays in a way that I could use as a measure, but with no success. I was hoping that with the link in place (1 to 1 - Both) I would be able to set a measure of IF(X = Y,1,0), but this won't work either as it doesn't see my table that holds the Bank Holiday Dates.

 

Can anyone suggest why the lookup value won't work as a measure or if there is any other way around it.

 

Many Thanks

Rich.

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi RichWyeth,

     

    Finally, your expect result is to get the actual working days per month, right?

     

    If so, in the weekday table, please add these calculated columns:

    Holiday =
    LOOKUPVALUE ( 'Bank Holidays'[Dates], 'Bank Holidays'[Dates], Weekdays[Date] )

IF =
    IF ( Weekdays[Date] = Weekdays[Holiday], 0, 1 )

MonthName = MONTH(Weekdays[Date])
     
    Create a measure:
    Actual working days =
    CALCULATE ( SUM ( Weekdays[IF] ), ALLEXCEPT ( Weekdays, Weekdays[MonthName] ) )
     
    By the way, I didn't create a relationship between these two tables.
     
    If you still have any question, please feel free to ask.
     
    Best regards,
    Yuliana Gu



     

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi RichWyeth,

     

    Finally, your expect result is to get the actual working days per month, right?

     

    If so, in the weekday table, please add these calculated columns:

    Holiday =
    LOOKUPVALUE ( 'Bank Holidays'[Dates], 'Bank Holidays'[Dates], Weekdays[Date] )

IF =
    IF ( Weekdays[Date] = Weekdays[Holiday], 0, 1 )

MonthName = MONTH(Weekdays[Date])
     
    Create a measure:
    Actual working days =
    CALCULATE ( SUM ( Weekdays[IF] ), ALLEXCEPT ( Weekdays, Weekdays[MonthName] ) )
     
    By the way, I didn't create a relationship between these two tables.
     
    If you still have any question, please feel free to ask.
     
    Best regards,
    Yuliana Gu