Forum Discussion

mallap849's avatar
mallap849
Helper I
3 years ago

Filter and summarize function

Hello everyone,

I have a dataset named ‘Vehicle Parts’ from which I want to first filter the variable [ACCOUNT NAME] by ‘vehicle parts -warehouse’ and then I want to group by the variables – [TRANS_DATE] is in date format, [ORG] – numeric variable, [ORG_NAME] – is a string. Then, I want to sum the variable [TRANS VALUE].

I am using the following command:

Expense =

VAR K =

  FILTER('Vehicle Parts', 'Vehicle Parts'[ACCOUNT_NAME] = "Vehicle Parts - Warehouse" )

  RETURN

  CALCULATETABLE(

      SUMMARIZE(

          'Vehicle Parts',

          'Vehicle Parts'[TRANS_DATE].[Date],'Vehicle Parts'[ORG],'Vehicle Parts'[ORG_NAME],

          "EXP" , SUM('Vehicle Parts'[TRANS VALUE])

      ),

      KEEPFILTERS( k )

  )

 

 I don’t get any error, but I know it is not correct because it does not give me the correct information.

Thank you in advance for all the help.

Need Help

7 Replies

    • mallap849's avatar
      mallap849
      Helper I
      3 years ago

      Hi Ilebdlin, 

       

      I did so as to keep the impact of filter of 

      FILTER('Vehicle Parts', 'Vehicle Parts'[ACCOUNT_NAME] = "Vehicle Parts - Warehouse" )

      Is that not correct?

      Thanks!

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        KEEPFILTERS allows you to "add on"  rather than "take away"  (which is the default behavior for filters). 

         

        So when you say [Color]="blue" && [Color]="red"  this means the color should be blue and red at the same time which usually results in BLANK().  But with KEEPFILTERS you can add the "red"  filter to a context that is already filtered to "blue", effectively resulting in [Color]="blue" || [Color]="red"  or simpler [Color] IN {"blue","red"}

         

        Please provide sanitized sample data that fully covers your issue.
        Please show the expected outcome based on the sample data you provided.