Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filterall calulation does not work in variables

Good sunday everyone

 

Does anyone know why when I use  filter(all()) calculations, if it is in a variable measure it does not work but as a separate measure it will work. 

 

For e.g., 

VAR totalsalesofshoes = calculate(sum(totalsales), type = shoes 

return calculate(totalsalesofshoes,(filter(all(calendar),index>

= max(dim_Calendar[Index])-5 && dim_Calendar[Index]<= MAX(dim_Calendar[Index]))))
 
but if split the return portion into a separate measure, it calculates correctly. If I don't split it the max index portion does not get calculated at all. 

 

Need Help

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    Anonymous 

    You cannot use a variable like "totalsalesofshoes" as n expression inside CALCULATE. The variable will not be affected by filters therein.


  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Ohh thanks for the article. Then what do u suggest I do? Create a separate measure or is there another solution?

    • jdbuchanan71's avatar
      jdbuchanan71
      Super User
      4 years ago

      Yes, make it into two measures.

      totalsalesofshoes =
    CALCULATE ( SUM ( totalsales[Sales] ), type = shoes )
      Final Measure =
CALCULATE (
    [totalsalesofshoes],
    FILTER (
        ALL ( calendar ),
        index
            >= MAX ( dim_Calendar[Index] ) - 5
            && dim_Calendar[Index] <= MAX ( dim_Calendar[Index] )
    )
)