Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
4 years ago
Solved

The function COUNT cannot work with values of type Boolean

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(...
Need Help
  • ArchStanton's avatar
    ArchStanton
    4 years ago

    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)

     

     

OzkanDhont's avatar
OzkanDhont
Resolver II
4 years ago

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

  • ArchStanton's avatar
    ArchStanton
    Power Participant
    4 years ago

    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)