Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
hashtag_pete
Helper V
Helper V

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

 

2 ACCEPTED SOLUTIONS

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors