Forum Discussion
I am using below query but getting error
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
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
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]) - 1VAR SelectedYear = SELECTEDVALUE('growth factor'[year1])VAR FilterCondition = FILTER(ALL('growth factor'),'growth factor'[Names]=selectedName&&'growth factor'[Variable_quarter]=SelectedQUarter&&'growth factor'[year1]=SelectedYear)RETURNCALCULATE(SUM('growth factor'[AMT]),FilterCondition)(This is also much more readable/easier to debug)For an overview of variables in DAX see hereI've created a PBIX with some dummy data for that format hereNote 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
2 Replies
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]) - 1VAR SelectedYear = SELECTEDVALUE('growth factor'[year1])VAR FilterCondition = FILTER(ALL('growth factor'),'growth factor'[Names]=selectedName&&'growth factor'[Variable_quarter]=SelectedQUarter&&'growth factor'[year1]=SelectedYear)RETURNCALCULATE(SUM('growth factor'[AMT]),FilterCondition)(This is also much more readable/easier to debug)For an overview of variables in DAX see hereI've created a PBIX with some dummy data for that format hereNote 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