Lookup Values and Measures

[RichWyeth]

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.

[v-yulgu-msft · Accepted solution]

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

[RichWyeth]

Thanks @v-yulgu-msft

Always learning something new, long way to go but getting there.