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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors