Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mallap849
Helper I
Helper I

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.

7 REPLIES 7
lbendlin
Super User
Super User

What made you choose KEEPFILTERS?

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!

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.

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

mallap849_0-1680629197695.png

 

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

mallap849_1-1680629255625.png

 

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 

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.

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!

For the first part you don't need DAX

 

lbendlin_0-1680837006698.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.