Forum Discussion
Filter and summarize function
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
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-p/963216
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
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.