A function 'DATESBETWEEN' has been used in a True/False expression that is used as a table filter

[FreyeDW]

Still new to Power Bi and writing DAX and the obligatory First Post Warning!!

My thought was to write a measure counting rows in a table where I have one date range with a hard coded date range for one filter than needing another filter where another date range from a different column will be equal to another date range from another different column. 

I think I also need to use an ALLEXCEPT so the visual wont inherit the filter from two of my slicers. I only want my 

'PT BASE'[Division] and 

'PT BASE'[Perf Provider] slicers to work.

I've been researching and trying different methods just trying to get the measure to work first before I started messing with the exclusion of the slicers. 

A function 'DATESBETWEEN' has been used in a True/False expression that is used as a table filter expression. This is not allowed is the error I keep getting. 

I've tried variables, creating multiple measures and using those and I've seen a few solutions that look like they might work, but I still get errors. Any help would be appreciated.

# Seen 08/01/2022-03/31/2023 and Admin equals Recorded 08/01/2022-03/31/2023 =

var _table =

CALCULATETABLE('PT BASE'

        ,DATESBETWEEN('PT BASE'[EncounterDTTM],DATE ( 2022, 08, 01 ),DATE ( 2023, 03, 31 ))

        ,DATESBETWEEN('PT BASE'[Vaccine Administered Date],DATE ( 2022, 08, 01 ),DATE ( 2023, 03, 31 )) = DATESBETWEEN('PT BASE'[Vaccine Recorded Date],DATE ( 2022, 08, 01 ),DATE ( 2023, 03, 31 )))

return

COUNTROWS( _table )+0

[JCDaling06]

Hi! @mfeyx  I am hoping to piggy back off of this post.  I have a very similar issue and I am trying to find a distinct count of ID numbers who have donated before a certain date, after a certain date, and NOT during a certain date range.  I have tried to modify this as 

Retention measure = CALCULATETABLE('Table1', DATESBETWEEN(Table1[Gift date],DATE(2023,07,01),DATE(2024,06,30)), NOT(DATESBETWEEN(Table1[Gift date],DATE(2022,07,01), DATE(2023,60,30))), DATESBETWEEN(Table1[Gift date],DATE(1980,01,01), DATE(2022,06,30))) return COUNTROWS (_table)+0

I get the error message "the syntax 'return' is incorrect.  Any ideas why?  I have tried as a column, measure, and table.  I am very new to powerbi so any help would be greatly appreciated!

[MFelix]

Hi  

The problem is that you did not defined the variable just need toadd vat _table.

Here is the full code

Retention measure =
VAR _table =
    CALCULATETABLE (
        'Table1',
        DATESBETWEEN ( Table1[Gift date], DATE ( 2023, 07, 01 ), DATE ( 2024, 06, 30 ) ),
        NOT (
            DATESBETWEEN ( Table1[Gift date], DATE ( 2022, 07, 01 ), DATE ( 2023, 60, 30 ) )
        ),
        DATESBETWEEN ( Table1[Gift date], DATE ( 1980, 01, 01 ), DATE ( 2022, 06, 30 ) )
    )
RETURN
    COUNTROWS ( _table ) + 0

[JCDaling06]

Hi!  Thanks for you response! When i modified that I got this error message "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."  not sure what to do with that. 

Thanks!

[MFelix]

Hi @FreyeDW ,

The issue is the part of the formula where you do:

DATESBETWEEN (
            'PT BASE'[Vaccine Administered Date],
            DATE ( 2022, 08, 01 ),
            DATE ( 2023, 03, 31 )
        )
            = DATESBETWEEN (
                'PT BASE'[Vaccine Recorded Date],
                DATE ( 2022, 08, 01 ),
                DATE ( 2023, 03, 31 )
            )

You are comparing to datesbetween and that is not allowed that is the error you are getting believe that you need to redo the measure with a comma and not an equal:

# Seen 08/01/2022-03/31/2023 and Admin equals Recorded 08/01/2022-03/31/2023 =
VAR _table =
    CALCULATETABLE (
        'PT BASE',
        DATESBETWEEN (
            'PT BASE'[EncounterDTTM],
            DATE ( 2022, 08, 01 ),
            DATE ( 2023, 03, 31 )
        ),
        DATESBETWEEN (
            'PT BASE'[Vaccine Administered Date],
            DATE ( 2022, 08, 01 ),
            DATE ( 2023, 03, 31 )
        ),
        DATESBETWEEN (
            'PT BASE'[Vaccine Recorded Date],
            DATE ( 2022, 08, 01 ),
            DATE ( 2023, 03, 31 )
        )
    )
RETURN
    COUNTROWS ( _table ) + 0

Not sure if I got the correct result you want to achieve only from the description but your error is because of the issue above identified.

If this formula does not return expected result can you share some more insight on what is the result you want to have.

[FreyeDW]

Thank You, I am validating that solution right now, so far it looks good!

So then the next question would be since this is where the Admin equals Recorded, how would the measure go if I need Admin NOTequal to Recorded?

[MFelix · Accepted solution]

Hi @FreyeDW ,

If you need the admin not to be in that period then you should use something similar to this:

# Seen 08/01/2022-03/31/2023 and Admin equals Recorded 08/01/2022-03/31/2023 =
VAR _table =
    CALCULATETABLE (
        'PT BASE',
        DATESBETWEEN (
            'PT BASE'[EncounterDTTM],
            DATE ( 2022, 08, 01 ),
            DATE ( 2023, 03, 31 )
        ),
       NOT( DATESBETWEEN (
            'PT BASE'[Vaccine Administered Date],
            DATE ( 2022, 08, 01 ),
            DATE ( 2023, 03, 31 )
        )),
        DATESBETWEEN (
            'PT BASE'[Vaccine Recorded Date],
            DATE ( 2022, 08, 01 ),
            DATE ( 2023, 03, 31 )
        )
    )
RETURN
    COUNTROWS ( _table ) + 0

Again without data is diffiicult to pin point if this works.

[FreyeDW]

Thank You! This is looking great so far. Now I know where to put that NOT.