Forum Discussion

pintoan's avatar
pintoan
Frequent Visitor
2 years ago
Solved

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?

Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

     

     

     

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    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

     

     

     

     

    • pintoan's avatar
      pintoan
      Frequent Visitor
      2 years ago

      thank you, perfect! marked as solution.