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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Edit DAX to filter out specifics dates from column

Hi,

I have some DAX that is filtering out weekends. But I also need it to filter out specifcs company holidays. He is the DAX i'm currently using. I need the dates removed from the date column.

 

dim date = 
FILTER (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
        "WeekDay", WEEKDAY ( [Date], 2 )
    ),
    [WeekDay] <= 5
)

 

 

Here is a picture of my two columns. I need to remove Christmas, Thanksgiving and so on. Any help is appreciated.

jghfgd.PNG

1 ACCEPTED SOLUTION

Sorry, you need a SELECTCOLUMNS in there:

 

dim date = 
EXCEPT(
  SELECTCOLUMNS(
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
            "WeekDay", WEEKDAY ( [Date], 2 )
        ),
        [WeekDay] <= 5
    ),
    "Date",[Date]
  ),
  ALL('Holidays',[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

10 REPLIES 10
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

You will need a Holiday table. Refer the topic below about how to create Holiday table.

https://community.powerbi.com/t5/Desktop/Public-Holiday-Script/m-p/178341.

Mark the holidays and weekends then filter the tag in your formula.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Greg_Deckler
Super User
Super User

Create a Holidays table, use EXCEPT.


@ 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...
Anonymous
Not applicable

Hi @Greg_Deckler,
Thanks for replying. I do have a holidays table. And whenever I try to add anything to that existing dax it breaks it. Where within that dax would I put EXCEPT?

Should be something along the lines of:

 

dim date = 
EXCEPT(
  FILTER (
      ADDCOLUMNS (
          CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
          "WeekDay", WEEKDAY ( [Date], 2 )
      ),
      [WeekDay] <= 5
  ),
  ALL('Holidays',[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...
Anonymous
Not applicable

Hi @Greg_Deckler ,

Thanks for reaching out so quickly. Looks like I'm still having an issue.Here are some screen shots of what's going on. I'm a bit confused. I tried two different ways. I hvae included a screen shot of the holiday table. Maybe the dim date table isn't right or something.error 1.PNGerror 2.PNGholiday.PNG

@Anonymous  my bad, it should be ALL('Holidays'[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...
Anonymous
Not applicable

@Greg_DecklerI did that example in the first picture I sent and got an error.error 1.PNG

Sorry, you need a SELECTCOLUMNS in there:

 

dim date = 
EXCEPT(
  SELECTCOLUMNS(
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
            "WeekDay", WEEKDAY ( [Date], 2 )
        ),
        [WeekDay] <= 5
    ),
    "Date",[Date]
  ),
  ALL('Holidays',[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...
Anonymous
Not applicable

@Greg_DecklerGreg, my man! Now that I see it and how it works, it makes sense. Thanks for all you do in the community.

My apologies for all of the syntax errors! That's what happens when I try to write DAX without testing it!!


@ 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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