Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Sum/Filter Help

I'm stumped!   

 

I have the following measure:

 

SAS_Testing:=

SAS-TESTING = 
CALCULATE(SUM(Table1[AMOUNT]),ALL('Name'[NAME]),Table1[Dept]="SAS",
Table1[sales_id]=0,table1[product_id]="GENOH")

Which gives me the exact results i want.  I try using the following (which should provide the maximum value in column product_id for name selected):

 

SAS-Max:=
calculate(max(product_id),allselected('name'[name]))


SAS-TESTING = 
CALCULATE(SUM(Table1[AMOUNT]),ALL('Name'[NAME]),Table1[Dept]="SAS",
Table1[sales_id]=0,table1[product_id]=[sas-max])

 

When I use this i'm greeted with the friendly "a function 'Calculate' has been used in a true/false blah blah blah"  HELP!!!

Need Help

4 Replies

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    Hello,

     

    Is your product id numeric?

    In your first sample you write [product_id]="GENOH".

     

    I'm wondering that calculate can handle this equations as filter. I expected, you have to wrap it in a FILTER formula. But obviously that's not an issue.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      The column is alphanumeric (but typically contains all text).  I'm wondering if max is the wrong operator to be using to handle strings

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Anonymous

         

        Try this

         

        SAS-TESTING =
VAR mymeasure = [sas-max]
RETURN
    CALCULATE (
        SUM ( Table1[AMOUNT] ),
        ALL ( 'Name'[NAME] ),
        Table1[Dept] = "SAS",
        Table1[sales_id] = 0,
        table1[product_id] = mymeasure
    )