Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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] =SELECT...
Need Help
  • rsaprano's avatar
    rsaprano
    6 years ago

    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
rsaprano's avatar
rsaprano
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    This solution Works ! Thanks 🙂