Error with calendar function with blank start or end dates

[pintoan]

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?

[OwenAuger · Accepted solution]

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]

thank you, perfect! marked as solution.