Forum Discussion

sputnik76's avatar
sputnik76
Frequent Visitor
7 years ago
Solved

DAX filter based on Todays date?

Hi - can anyone help?

 

Am trying to calculate a sum with multiple filters including one that looks at a date in a field and should add a filter if the date is LESS THAN today.    The error this produces is that a "function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

Here is my DAX expression:

 

Max Possible Hours = CALCULATE(COUNT(users[full name]),FILTER(users,users[is_active]= True), FILTER('Harvest Inactive List','Harvest Inactive List'[inactive date] < TODAY())*[Workdays]*7.6)
 
It takes the user, filters it by whether they are active or not and then (the bit that doesn't work) should filter by another table (with same user names in it) and a 'inactive date'.  I want to include in the sum the data where 'inactive date' is less than TODAY. Or in other word exclude users who have an inactive date that is in the past.
 
How can I made a filter that filters out some things based on todays date?
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi sputnik76 

     

    Believe that the problem with your measure is where you have is the [Workdays] * 7.6 , when using the CALCULATE function the first parameter is the calculation from the second parameter on you must use the filters you want to apply in this case you are applying a multiplication as a filter so the check is if that multiplication is true or false.

     

    Another problem may be where you refer TRUE if this is refering to a result of TRUE/FALSE you need to use the TRUE() if its a written value on your column you should use the "" to make it as text

     

    Check the two measures below:

     

     

    Max Possible Hours =
CALCULATE (
    COUNT ( users[full name] ) * [Workdays] * 7.6,
    FILTER ( users, users[is_active] = TRUE() ),
    FILTER (
        'Harvest Inactive List',
        'Harvest Inactive List'[inactive date] < TODAY ()
    ) 
)

     

     

     

    Max Possible Hours =
CALCULATE (
    COUNT ( users[full name] ) * [Workdays] * 7.6,
    FILTER ( users, users[is_active] = "TRUE" ),
    FILTER (
        'Harvest Inactive List',
        'Harvest Inactive List'[inactive date] < TODAY ()
    ) * [Workdays] * 7.6
)

     

     

    Regards,

    Mfelix

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi sputnik76 

     

    Believe that the problem with your measure is where you have is the [Workdays] * 7.6 , when using the CALCULATE function the first parameter is the calculation from the second parameter on you must use the filters you want to apply in this case you are applying a multiplication as a filter so the check is if that multiplication is true or false.

     

    Another problem may be where you refer TRUE if this is refering to a result of TRUE/FALSE you need to use the TRUE() if its a written value on your column you should use the "" to make it as text

     

    Check the two measures below:

     

     

    Max Possible Hours =
CALCULATE (
    COUNT ( users[full name] ) * [Workdays] * 7.6,
    FILTER ( users, users[is_active] = TRUE() ),
    FILTER (
        'Harvest Inactive List',
        'Harvest Inactive List'[inactive date] < TODAY ()
    ) 
)

     

     

     

    Max Possible Hours =
CALCULATE (
    COUNT ( users[full name] ) * [Workdays] * 7.6,
    FILTER ( users, users[is_active] = "TRUE" ),
    FILTER (
        'Harvest Inactive List',
        'Harvest Inactive List'[inactive date] < TODAY ()
    ) * [Workdays] * 7.6
)

     

     

    Regards,

    Mfelix