Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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 ...
Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    Hi Anonymous ,

     

    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.

JCDaling06's avatar
JCDaling06
Regular Visitor
2 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      JCDaling06
      Regular Visitor
      2 years ago

      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!