Filter and summarize function

[mallap849]

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.

[lbendlin]

What made you choose KEEPFILTERS?

[mallap849]

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]

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.

[mallap849]

Sorry for some reason I cannot upload the .pbix file. But I hope screen shots below maske a better sense? Thank you, for the help!

Vehicle Parts - Table

From the above data I want to create a table that only selects – ACCOUNT_NAME  “Vehicle Parts- Warehouse” and that groups by variables – TRANS_DATE, ORG, ORG NAME and SUM – TRANS VALUE

DAX used to create a new table.

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 want to create this table so that I can connect it with another table, below – Car Mileage Data, based two variables.

Car Mileage Data

Originally, I thought of create a table based on two tables above. Vehicle Parts data had Trans Value and Car Mileage Data has the METER_1_USAGE that I want in one table.

Regards,

Priyanka 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

[mallap849]

Link:

https://1drv.ms/u/s!AiHR2qZdQykFgUMVU8Ct_SEqIn_E?e=PrfHoj 

Background:

 have three tables in the .pbix file.

1. Vehicle Parts 2. Care Mileage Date and 3.PPE ORG.

First I created a sub table called Expense- Table. It is referenced to Vehicle Parts data. For this table, I want to

1. filter the variable – ACCOUNT_NAME by Vehicle Parts – Warehouse.

2. group by variables – TRANS DATE, ORG, ORG NAME and

3. sum TRANS VALUE.

Then from PPE ORG I bring in the Org variable in the Car Mileage Data. I do this by adding a column in Car Mileage Data.

I tried to link the two table by Date and Org - Many to Many Relationship but this did not work.

Expected Result:

The result I want from the two table I.E., Expense Table and Car Mileage Data

Trans_ Date	ORG	ORG_NAME	TRANS VALUE	METER_1_USAGE	CLASS_CLASS_MAINT

I hope this helps. Thanks again!

[lbendlin]

For the first part you don't need DAX

To link the two tables you would use a composite key (date + org) but your car_mileage table is not clean/not covering.

Neither your Expense_table nor your PPE Org table are required.