Calculating measure based on many conditions and filters

[mhablas426]

Dears,
I would like to calculate the MTD Amount of a brand based on different categories they can reach over 5 categories in some cases.
I tried to use this but got figures from other categories not included in the measure so what is the best Dax I can use or write to fetch the data?

SPIRAX 30 MTD DT =

CALCULATE (

    SUM ( 'BI Oil Consumption'[Equipment Quantity] ),

    MONTH ( 'Oil Dim Date'[Date] ) = MONTH ( TODAY () ),

    YEAR ( 'Oil Dim Date'[Date] ) = YEAR ( TODAY () ),

    FILTER ( 'Oil Type', 'Oil Type'[Equipment Catagory] = "Dump Trucks 785C" ),

    FILTER ( 'Oil Type', 'Oil Type'[Equipment Catagory] = "Dump Trucks 775G" ),
    FILTER ( 'Oil Type', 'Oil Type'[Equipment Catagory] = "ABC" ),

    FILTER ( 'Oil Type', 'Oil Type'[Oil Brand And Grade] = "SPIRAX S4 CX30 SAE 30" )

)

I really appreciate any help you can provide.

[mhablas426]

30 MTD DT TEST =

CALCULATE (

    SUM ( 'BI Oil Consumption'[Equipment Quantity] ),

    MONTH ( 'Oil Dim Date'[Date] ) = MONTH ( TODAY () ),

    YEAR ( 'Oil Dim Date'[Date] ) = YEAR ( TODAY () ),

    FILTER (

        'Oil bridge equipment',

        'Oil bridge equipment'[Equipment Catagory]

            IN { "Dump Trucks 785C", "Dump Trucks 775G" }

    ),

    FILTER ( 'Oil Type', 'Oil Type'[Oil Brand And Grade] = "SPIRAX S4 CX30 SAE 30" )

)

Yes I tried and it works with me 
Thank you too much

[mhablas426]

I would thank you too much for your reply,
I tried but it still fetches data from other categories.
the snapshot below shows the relationship between the tables
I think I have to use a category from the "oil bridge equipment table" not from the same table!

[OktayPamuk80]

Nop, filter direction can be the problem between oil type and component. Make the relationship to filter both. It should be type filtering component, but somehow you seem to have multiple types per component. Maybe duplicates or data quality issues?

[mhablas426]

About using TotalMTD I found figures in the new month although there is no quantity issued yet, and I don't know how that happened, so I used month() instead of it.
yes I have multiple types per component I can share with you the file if you would to take a look.
https://drive.google.com/drive/folders/1Ev2mfRdHV615--SxQWBuypfL96BTdHm2?usp=drive_link 

[OktayPamuk80]

Hi,

I requested access to the google drive folder. You should have received an email.

[mhablas426]

Access is granted.

Sorry for the late.

[OktayPamuk80]

Hi Mohamed,

The reason is the table Oil Type is connected using the component. However, if you try to filter on catagory, because of the connection over component, it can only filter on the values hydraulic and transmission, therefore, showing you all categories in these components. I would connect the oil type directly to the BI oil consumption over the category (in the Oil consumption you have that field too and have the component only in the other component table), remove component from the oil type and remove duplicates (in Power Query).

Then filtering works.

Regards,
Oktay

[mhablas426]

Hi Oktay,
I will try this process,
thanks for your time and effort.

[OktayPamuk80]

By the way, for MTD I advice using

TOTALMTD(SUM ( 'BI Oil Consumption'[Equipment Quantity] ))

instead of:

SUM ( 'BI Oil Consumption'[Equipment Quantity] ),

    MONTH ( 'Oil Dim Date'[Date] ) = MONTH ( TODAY () ),

    YEAR ( 'Oil Dim Date'[Date] ) = YEAR ( TODAY () ),

[OktayPamuk80 · Accepted solution]

Hi,

First, I would have a filter for each field just one Filter function and as the filter fields come from one table, you can combine them into same filter function:

FILTER ( 'Oil Type',
'Oil Type'[Equipment Catagory] in { "Dump Trucks 785C" , "Dump Trucks 775G", "ABC" } &&

'Oil Type'[Oil Brand And Grade] = "SPIRAX S4 CX30 SAE 30" )

Can you give it a try?

Regards,
Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.