NOT IN function for holidays

[hashtag_pete]

Hello folks, 

I have a very simple calendar table which I want to enrich with holiday information in the "WorkingDay" column. For this I have pulled in a table from the internet which has the public holiday information, containing a date column and a description column. It looks so easy but I can't get it to work.
I want to have the boolean expression in "WorkingDay" taking the holidays into account.

Any ideas?

I thought adding a variable of the holiday dates and calling this in a NOT [Date] in {holidays} function would do it, but no...

Calendar = 
    GENERATE( 
        CALENDARAUTO(),
        var YYYY = 
            YEAR( [Date] )
        var MMM = 
            MONTH( [Date] )
        var Holidays = 
            {DISTINCT(Feiertage[Column2])}
        return

        ROW(
            "Year", YYYY,
            "Month", FORMAT( [Date] , "mmmm" ), 
            "Month No", MMM,
            "Weekday", FORMAT( [Date], "dddd"), 
            "Weekday No", WEEKDAY( [Date], 2),
            "WorkingDay", NOT ( 
                WEEKDAY( [Date], 2) in  {6,7} 
                )       
        )
        )

[Greg_Deckler · Accepted solution]

@hashtag_pete How about:

Calendar = 
    GENERATE( 
        CALENDARAUTO(),
        var YYYY = 
            YEAR( [Date] )
        var MMM = 
            MONTH( [Date] )
        var Holidays = 
            {DISTINCT(Feiertage[Column2])}
        return

        ROW(
            "Year", YYYY,
            "Month", FORMAT( [Date] , "mmmm" ), 
            "Month No", MMM,
            "Weekday", FORMAT( [Date], "dddd"), 
            "Weekday No", WEEKDAY( [Date], 2),
            "WorkingDay", NOT ( 
                WEEKDAY( [Date], 2) in  {6,7} 
                ) && NOT(CONTAINS(Holidays,[Column2],[Date]))
        )
        )

[Greg_Deckler]

@hashtag_pete Maybe:

Calendar = 
    GENERATE( 
        CALENDARAUTO(),
        var YYYY = 
            YEAR( [Date] )
        var MMM = 
            MONTH( [Date] )
        var Holidays = 
            {DISTINCT(Feiertage[Column2])}
        return

        ROW(
            "Year", YYYY,
            "Month", FORMAT( [Date] , "mmmm" ), 
            "Month No", MMM,
            "Weekday", FORMAT( [Date], "dddd"), 
            "Weekday No", WEEKDAY( [Date], 2),
            "WorkingDay", NOT ( 
                WEEKDAY( [Date], 2) in  {6,7} 
                ) && NOT([Date] in Holidays)
        )
        )

[hashtag_pete]

I gave this a try but received this error:
<pi>A table of multiple values was supplied where a single value was expected.</pi>

I even tried varing in making the holidays a list, but it doesn't seem to matter if I set the {} in the variable or in the function...

[Greg_Deckler · Accepted solution]

@hashtag_pete How about:

Calendar = 
    GENERATE( 
        CALENDARAUTO(),
        var YYYY = 
            YEAR( [Date] )
        var MMM = 
            MONTH( [Date] )
        var Holidays = 
            {DISTINCT(Feiertage[Column2])}
        return

        ROW(
            "Year", YYYY,
            "Month", FORMAT( [Date] , "mmmm" ), 
            "Month No", MMM,
            "Weekday", FORMAT( [Date], "dddd"), 
            "Weekday No", WEEKDAY( [Date], 2),
            "WorkingDay", NOT ( 
                WEEKDAY( [Date], 2) in  {6,7} 
                ) && NOT(CONTAINS(Holidays,[Column2],[Date]))
        )
        )

[hashtag_pete]

@Greg_Deckler not quite sure why it works but it does 😄

I thought that the IN operator is just syntax sugar for contains, but obviously in the background something is working differently. Thanks for pointing that one out

[Greg_Deckler · Accepted solution]

@hashtag_pete Well, I actually think that this is the better solution (below). I think that it is working because it ends up referencing a column in an actual table, [Column2] because the Holidays variable would have a column name of [Value] and not [Column2]. So probably a bit of luck. However, if you do it this way is better:

Calendar = 
VAR Holidays = DISTINCT(Feiertage[Column2])
RETURN
    GENERATE( 
        CALENDARAUTO(),
        var YYYY = 
            YEAR( [Date] )
        var MMM = 
            MONTH( [Date] )
        return
        ROW(
            "Year", YYYY,
            "Month", FORMAT( [Date] , "mmmm" ), 
            "Month No", MMM,
            "Weekday", FORMAT( [Date], "dddd"), 
            "Weekday No", WEEKDAY( [Date], 2),
            "WorkingDay", NOT ( 
                WEEKDAY( [Date], 2) in  {6,7} 
                ) && NOT([Date] in Holidays)
        )
        )