Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
1 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

View solution in original post

2 REPLIES 2

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
Not applicable

This solution Works ! Thanks 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.