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

 

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