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)

     

     

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
4 years ago

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() ) )))))

 

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

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'.

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.

  • ArchStanton's avatar
    ArchStanton
    Icon for Power Participant rankPower Participant
    4 years ago

    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")))