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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mhablas426
Advocate II
Advocate II

Calculating measure based on many conditions and filters

Screenshot 2024-10-05 234557.jpg

 

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.

1 ACCEPTED SOLUTION
OktayPamuk80
Resolver II
Resolver II

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.

 

View solution in original post

10 REPLIES 10
mhablas426
Advocate II
Advocate II

Screenshot 2024-10-06 004144.jpg

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
Advocate II
Advocate II

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!Screenshot 2024-10-06 002900.jpg

 

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?

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 

Hi,

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

Access is granted.

Sorry for the late.

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

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

OktayPamuk80
Resolver II
Resolver II

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
Resolver II
Resolver II

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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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