I am using below query but getting error

[Anonymous]

Growth For Quarter1 = CALCULATE(SUM('growth factor'[AMT]),

FILTER(ALL('growth factor'),

'growth factor'[NAMES] = SELECTEDVALUE('growth factor'[NAMES]) &&

'growth factor'[Variable_quarter] =SELECTEDVALUE('growth factor'[Variable_quarter])-1) &&

'growth factor'[year1] = SELECTEDVALUE('growth factor'[year1])

)
Error Received : A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

i am trying to get the sum of fee amount of last quarter in comparison to present quarter to get the growth factor.
i have created a quarter number and named it variable_quarter
it works fine for name and variable quarter but when i add year in filter it fails with the above error

[rsaprano · Accepted solution]

Hi  @Anonymous 

Not sure exactly what's exactly wrong with the DAX formula but I've re-written it using variables and it seems to work fine:

Growth For Quarter1 - V2 =

VAR selectedName = SELECTEDVALUE('growth factor'[Names])

VAR SelectedQUarter = SELECTEDVALUE('growth factor'[Variable_quarter]) - 1

VAR SelectedYear = SELECTEDVALUE('growth factor'[year1])

VAR FilterCondition = FILTER(ALL('growth factor'),'growth factor'[Names]=selectedName&&'growth factor'[Variable_quarter]=SelectedQUarter&&'growth factor'[year1]=SelectedYear)

RETURN

CALCULATE(SUM('growth factor'[AMT]),FilterCondition)

(This is also much more readable/easier to debug)

For an overview of variables in DAX see here

I've created a PBIX with some dummy data for that format here

Note that the performance of this measure (using FILTER(ALL( on an entire table) is likely to be very slow over a large data volume and is not the most optimal way to do time intelligence/work out growth factors - please reach out if you'd like to know about some other ways

[Anonymous]

This solution Works ! Thanks 🙂