cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Reply
ArchStanton
Post Patron
Post Patron

The function COUNT cannot work with values of type Boolean

‎08-16-2022 06:48 AM

Hi,

 

The code below works but it references weekday instead of the IS Working Day column which contains just a TRUE / FALSE value.

 

 

Total Deferral Lengthv2 = 
var _mindate=
MINX(FILTER(ALL('Deferrals'),'Deferrals'[regardingobjectid]=MAX('Deferrals'[regardingobjectid])),[actualstart])
var _maxdate=
MaxX(FILTER(ALL('Deferrals'),Deferrals[regardingobjectid]=MAX('Deferrals'[regardingobjectid])),[Closed Date])
return
CALCULATE(
    COUNT('Date Dimension'[Weekday]),FILTER(ALL('Date Dimension'),
    'Date Dimension'[Date]>_mindate&&'Date Dimension'[Date]<=_maxdate&&NOT('Date Dimension'[Weekday] ) in {6,7}))

 

 

so, I have my own Date Table that contains an IS WORKING DAY (TRUE / FALSE)

 

When I try to amend the code above I get the COUNT cannot work with Boolean error message

 

 

Total Deferral Lengthv2 copy = 
var _mindate=
MINX(FILTER(ALL('Deferrals'),'Deferrals'[regardingobjectid]=MAX('Deferrals'[regardingobjectid])),[Case Received])
var _maxdate=
MaxX(FILTER(ALL('Deferrals'),Deferrals[regardingobjectid]=MAX('Deferrals'[regardingobjectid])),[Closed Date])
return
CALCULATE(
    COUNT('Date Dimension'[Is Working Day]),FILTER(ALL('Date Dimension'),
        'Date Dimension'[Date]>_mindate&&'Date Dimension'[Date]<=_maxdate&&NOT('Date Dimension'[Is Working Day] )))

 

 

 I would like to count the No of Working Days but I'm not sure how to?

Any help would be much appreciated.

 

Thanks,

Labels:
Message 1 of 8
1,450 Views
0
1 ACCEPTED SOLUTION
ArchStanton
Post Patron
Post Patron
In response to OzkanDhont

‎08-22-2022 07:29 AM

Hi,

 

Just an update, your Formula works almost, I've managed to re-run it and I get no error messages now.

 

The only thing thats different between a calculated column that I know is 100% correct and this measure is the No of Days difference

 

Your formula here produced 158 days:

 

 

Time in Deferral = 
VAR _mindate =
    MINX (
        FILTER (
            ALL ( 'Deferrals' ),
            'Deferrals'[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [actualstart]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            ALL ( 'Deferrals' ),
            Deferrals[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [actualend]
    )
RETURN
    CALCULATE (
        COUNT ( 'Date'[Weekday] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] > _mindate
                && 'Date'[Date] <= _maxdate
                && 'Date'[Is Working Day] = True()))

 

 

But the correct number is derived from this calculated column:

 

 

TimeinDeferral = 
    VAR _Start = 'Deferrals'[actualstart]
    VAR _End = 'Deferrals'[actualend]
    VAR _Table = FILTER(ALL('Date'),[Date] >= _Start && [Date] <= _End && [Is Working Day] = TRUE())
RETURN
    COUNTROWS(_Table)

 

 

View solution in original post

Message 3 of 8
1,382 Views
7 REPLIES 7
v-easonf-msft
Community Support
Community Support

‎08-22-2022 01:52 AM

Hi, @ArchStanton 

Coluld you tell me whether your problem has been solved?

As mentioned by @OzkanDhont ,it may have something to do with the data type of your column "Is Working Day".

If the Data Type of column is 'True/false', try the formula below.

veasonfmsft_0-1661157655653.png

Total Deferral Lengthv2 =
VAR _mindate =
    MINX (
        FILTER (
            ALL ( 'Deferrals' ),
            'Deferrals'[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [actualstart]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            ALL ( 'Deferrals' ),
            Deferrals[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [Closed Date]
    )
RETURN
    CALCULATE (
        COUNT ( 'Date Dimension'[Weekday] ),
        FILTER (
            ALL ( 'Date Dimension' ),
            'Date Dimension'[Date] > _mindate
                && 'Date Dimension'[Date] <= _maxdate
                && 'Date Dimension'[Weekday] =FALSE()
        )
    )

If the Data Type of column is 'Text', try the formula below.

Total Deferral Lengthv2 =
VAR _mindate =
    MINX (
        FILTER (
            ALL ( 'Deferrals' ),
            'Deferrals'[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [actualstart]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            ALL ( 'Deferrals' ),
            Deferrals[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [Closed Date]
    )
RETURN
    CALCULATE (
        COUNT ( 'Date Dimension'[Weekday] ),
        FILTER (
            ALL ( 'Date Dimension' ),
            'Date Dimension'[Date] > _mindate
                && 'Date Dimension'[Date] <= _maxdate
                && 'Date Dimension'[Weekday] =FALSE()
        )
    )

Best Regards,
Community Support Team _ Eason

 

Message 8 of 8
1,400 Views
0
v-easonf-msft
Community Support
Community Support

‎08-22-2022 01:52 AM

Hi, @ArchStanton 

Coluld you tell me whether your problem has been solved?

As mentioned by @OzkanDhont ,it may have something to do with the data type of your column "Is Working Day".

If the Data Type of column is 'True/false', try the formula below.

veasonfmsft_0-1661157655653.png

Total Deferral Lengthv2 =
VAR _mindate =
    MINX (
        FILTER (
            ALL ( 'Deferrals' ),
            'Deferrals'[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [actualstart]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            ALL ( 'Deferrals' ),
            Deferrals[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [Closed Date]
    )
RETURN
    CALCULATE (
        COUNT ( 'Date Dimension'[Weekday] ),
        FILTER (
            ALL ( 'Date Dimension' ),
            'Date Dimension'[Date] > _mindate
                && 'Date Dimension'[Date] <= _maxdate
                && 'Date Dimension'[Weekday] =FALSE()
        )
    )

If the Data Type of column is 'Text', try the formula below.

Total Deferral Lengthv2 =
VAR _mindate =
    MINX (
        FILTER (
            ALL ( 'Deferrals' ),
            'Deferrals'[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [actualstart]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            ALL ( 'Deferrals' ),
            Deferrals[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [Closed Date]
    )
RETURN
    CALCULATE (
        COUNT ( 'Date Dimension'[Weekday] ),
        FILTER (
            ALL ( 'Date Dimension' ),
            'Date Dimension'[Date] > _mindate
                && 'Date Dimension'[Date] <= _maxdate
                && 'Date Dimension'[Weekday] =FALSE()
        )
    )

Best Regards,
Community Support Team _ Eason

 

Message 4 of 8
1,400 Views
0
ArchStanton
Post Patron
Post Patron
In response to v-easonf-msft

‎08-22-2022 02:49 AM

Hi,

 

Thanks for your suggestions, I've tried them all and I still get errors. (Ps I've renamed Date Dimension to Date for better clarity

 

For this version I get the following error message:

 

The function COUNT cannot work with values of type Boolean:

 

CALCULATE(
    COUNT('Date Dimension'[Is Working Day]),FILTER(ALL('Date Dimension'),
        'Date Dimension'[Date]>_mindate&&'Date Dimension'[Date]<=_maxdate&&'Date Dimension'[Is Working Day] = TRUE)))

 

 

and get this for the latter:

TRUE with () =
The syntax for ')' is incorrect. (DAX(var _mindate= MINX( FILTER( ALL ('Deferrals'), 'Deferrals'[regardingobjectid]=MAX('Deferrals'[regardingobjectid])),[actualstart])var _maxdate= MaxX( FILTER( ALL('Deferrals'), Deferrals[regardingobjectid]=MAX('Deferrals'[regardingobjectid])),[actualend])returnCALCULATE( COUNT('Date'[Is Working Day]), FILTER(ALL('Date'), 'Date'[Date]>_mindate &&'Date'[Date]<=_maxdate && ('Date'[Is Working Day] = TRUE() ) )))))

 

Message 5 of 8
1,397 Views
0
v-easonf-msft
Community Support
Community Support
In response to ArchStanton

‎08-22-2022 03:18 AM

Hi, @ArchStanton 

Please convert the data type of the column [Is Working Day] to 'Text' Or perform count operations on other columns whose type is not 'True/false'.

veasonfmsft_1-1661163188744.png

then retry the formula:

Total Deferral Lengthv2 =
VAR _mindate =
    MINX (
        FILTER (
            ALL ( 'Deferrals' ),
            'Deferrals'[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [actualstart]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            ALL ( 'Deferrals' ),
            Deferrals[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [Closed Date]
    )
RETURN
    CALCULATE (
        COUNT ( 'Date Dimension'[Weekday] ),
        FILTER (
            ALL ( 'Date Dimension' ),
            'Date Dimension'[Date] > _mindate
                && 'Date Dimension'[Date] <= _maxdate
                && 'Date Dimension'[Weekday] = "True"
        )
    )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 6 of 8
1,395 Views
0
ArchStanton
Post Patron
Post Patron
In response to v-easonf-msft

‎08-22-2022 03:42 AM

Hi

 

If I change the Binary TRUE / FALSE for IS Working Day to text then it breaks a whole load of other measures and calculations I have in my data model. 

 

I did create a new column just to see what happens and the TRUE / FALSE turns into binary 1,s and 0's

I wrapped the "1" in quotes and a I got a result that said 15 for every record:

 

Total Deferral Lengthv2 = var _mindate= MINX( FILTER( ALL ('Deferrals'), 'Deferrals'[regardingobjectid]=MAX('Deferrals'[regardingobjectid])),[actualstart]) var _maxdate= MaxX( FILTER( ALL('Deferrals'), Deferrals[regardingobjectid]=MAX('Deferrals'[regardingobjectid])),[actualend]) return CALCULATE( COUNT('Date'[Is Working Dayv2]), FILTER(ALL('Date'), 'Date'[Date]>_mindate &&'Date'[Date]<=_maxdate && ('Date'[Is Working Dayv2] = "1")))

 

Message 7 of 8
1,392 Views
0
OzkanDhont
Resolver II
Resolver II

‎08-16-2022 02:31 PM

Hi @ArchStanton !

 

I believe the problem resided in the usage of NOT() function.
You're trying to return all the true values in column 'Is Working Day'?

 

Would you mind trying following DAX?:

 

CALCULATE(
    COUNT('Date Dimension'[Is Working Day]),FILTER(ALL('Date Dimension'),
        'Date Dimension'[Date]>_mindate&&'Date Dimension'[Date]<=_maxdate&&'Date Dimension'[Is Working Day] = TRUE)))

 

 

 

EDIT: You might have to use TRUE() instead of TRUE.

 

CALCULATE(
    COUNT('Date Dimension'[Is Working Day]),FILTER(ALL('Date Dimension'),
        'Date Dimension'[Date]>_mindate&&'Date Dimension'[Date]<=_maxdate&&'Date Dimension'[Is Working Day] = TRUE())))

Kind regards,

OD

Message 2 of 8
1,415 Views
ArchStanton
Post Patron
Post Patron
In response to OzkanDhont

‎08-22-2022 07:29 AM

Hi,

 

Just an update, your Formula works almost, I've managed to re-run it and I get no error messages now.

 

The only thing thats different between a calculated column that I know is 100% correct and this measure is the No of Days difference

 

Your formula here produced 158 days:

 

 

Time in Deferral = 
VAR _mindate =
    MINX (
        FILTER (
            ALL ( 'Deferrals' ),
            'Deferrals'[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [actualstart]
    )
VAR _maxdate =
    MAXX (
        FILTER (
            ALL ( 'Deferrals' ),
            Deferrals[regardingobjectid] = MAX ( 'Deferrals'[regardingobjectid] )
        ),
        [actualend]
    )
RETURN
    CALCULATE (
        COUNT ( 'Date'[Weekday] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] > _mindate
                && 'Date'[Date] <= _maxdate
                && 'Date'[Is Working Day] = True()))

 

 

But the correct number is derived from this calculated column:

 

 

TimeinDeferral = 
    VAR _Start = 'Deferrals'[actualstart]
    VAR _End = 'Deferrals'[actualend]
    VAR _Table = FILTER(ALL('Date'),[Date] >= _Start && [Date] <= _End && [Is Working Day] = TRUE())
RETURN
    COUNTROWS(_Table)

 

 

Message 3 of 8
1,383 Views

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

View All
Top Solution Authors
View All