topic Re: Lookupvalue from Date table and Return Holidays in DAX Commands and Tips

Lookupvalue from Date table and Return Holidays

Anonymous — Tue, 12 Oct 2021 23:53:53 GMT

Hi,

I have date table and holidays table. I've created calculated holiday column in date table to return holiday name from holidays table but I get error "A table of mulitple values was supplied where a single value was expected"

The holidays table includes 3 years of holidays so how do I return correct holiday for the corresponding year?

Holiday = LOOKUPVALUE('Holidays 2021-2023'[Holiday ],'Holidays 2021-2023'[Date],'Calendar'[Date])

Re: Lookupvalue from Date table and Return Holidays

wdx223_Daniel — Wed, 13 Oct 2021 01:14:10 GMT

it seems there are duplicate dates in the table of holidays.

Re: Lookupvalue from Date table and Return Holidays

Anonymous — Wed, 13 Oct 2021 02:24:06 GMT

wdx223_Danielyes there are because I have future holidays included in the table. How do I factor this into the measure to return holiday column based on the corresponding year date?

Re: Lookupvalue from Date table and Return Holidays

Jihwan_Kim — Wed, 13 Oct 2021 03:06:40 GMT

Hi,

In a calculated column in the date table, for instance, on 29th/Nov/2021, what holiday name do you want to show?

I can see that your holiday table has two different holiday names on 29th/Nov/2021, and also, some of the dates have two different holiday names on the same date.

Re: Lookupvalue from Date table and Return Holidays

Anonymous — Wed, 13 Oct 2021 03:42:26 GMT

Jihwan_Kim those holidays are for different regions of the country i.e. where holiday name has "Anniversary" in the suffix. Do I need to separate these "anniversary" holidays into a separate table?

Re: Lookupvalue from Date table and Return Holidays

wdx223_Daniel — Wed, 13 Oct 2021 04:51:41 GMT

try this

Calculated Column=concatenatex(filter(holidays_table,holidays_table[date]=calendar[date]),holidays_table[holiday],",")

Re: Lookupvalue from Date table and Return Holidays

Anonymous — Wed, 13 Oct 2021 06:17:28 GMT

wdx223_Daniel perfect that worked, thank you. Much appreciated.