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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.