Forum Discussion

hashtag_pete's avatar
hashtag_pete
Helper V
4 years ago
Solved

NOT IN function for holidays

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 pub...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    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's avatar
    Greg_Deckler
    4 years ago

    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)
        )
        )

     

     

hashtag_pete's avatar
hashtag_pete
Helper V
4 years ago

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's avatar
Greg_Deckler
Community Champion
4 years ago

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)
        )
        )