Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower 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)

     

     

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
4 years ago

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.

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