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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pintoan
Frequent Visitor

Error with calendar function with blank start or end dates

Hi everyone,

 

I have this code working to count the number of days there is between 2 dates.

VAR StartDate = MIN(_datemin_swapped, _datemax_swapped)
VAR EndDate = MAX(_datemin_swapped, _datemax_swapped)

VAR FilteredDates =
    FILTER(
        CALENDAR(StartDate, EndDate),
        WEEKDAY([Date]) IN {1, 7}  // 1 = Sunday, 7 = Saturday
    )


VAR WeekendCount = COUNTROWS(FilteredDates)

 
To ensure no blanks are in start and end date i use this and get the error:
The COUNTROWS function expects a table expression for argument '', but a string or numeric expression was used.

VAR FilteredDates =
    IF(
        ISBLANK(StartDate) || ISBLANK(EndDate) || StartDate = 0 || EndDate = 0,
        BLANK(),  -- Return BLANK() if either StartDate or EndDate is blank or zero
        FILTER(
            CALENDAR(StartDate, EndDate),
            WEEKDAY([Date]) IN {1, 7}  // 1 = Sunday, 7 = Saturday
        )
    )
VAR WeekendCount = COUNTROWS(FilteredDates)

 Any idea?

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @pintoan 

Two issues with your second expression:

  1. The argument of COUNTROWS must be a table.
  2. DAX does not allow IF or SWITCH to return tables.

The error message relates to point 1, since BLANK() is (possibly) passed as an argument of COUNTROWS.

 

Here is one way of fixing this (with some additional tweaks to the code):

VAR WeekendCount =
    IF (
        NOT ( StartDate = 0 || EndDate = 0 ), -- just test for 0 since BLANK() = 0
        VAR FilteredDates =
            FILTER (
                CALENDAR ( StartDate, EndDate ),
                WEEKDAY ( [Date] ) IN { 1, 7 } // 1 = Sunday, 7 = Saturday
            )
        RETURN
            COUNTROWS ( FilteredDates )
    )
  • Since DAX treats BLANK() = 0 we can just test for zero values.
  • Only compute FilteredDates and count its rows if the condition is met
  • Otherwise return BLANK() by default

Does this work for you?

 

Regards

 

 

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hi @pintoan 

Two issues with your second expression:

  1. The argument of COUNTROWS must be a table.
  2. DAX does not allow IF or SWITCH to return tables.

The error message relates to point 1, since BLANK() is (possibly) passed as an argument of COUNTROWS.

 

Here is one way of fixing this (with some additional tweaks to the code):

VAR WeekendCount =
    IF (
        NOT ( StartDate = 0 || EndDate = 0 ), -- just test for 0 since BLANK() = 0
        VAR FilteredDates =
            FILTER (
                CALENDAR ( StartDate, EndDate ),
                WEEKDAY ( [Date] ) IN { 1, 7 } // 1 = Sunday, 7 = Saturday
            )
        RETURN
            COUNTROWS ( FilteredDates )
    )
  • Since DAX treats BLANK() = 0 we can just test for zero values.
  • Only compute FilteredDates and count its rows if the condition is met
  • Otherwise return BLANK() by default

Does this work for you?

 

Regards

 

 

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

thank you, perfect! marked as solution.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors