cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
trdoan Member
Member

Help with DAX to add 1 more criterion to measure

Hi everyone,

 

I have this measure:

 

% Change MoM in AVG TAT = 
DIVIDE(
 CALCULATE(
 AVERAGE('full updated ALL PARTS'[Final Run Time]), 
 FILTER('full updated ALL PARTS','full updated ALL PARTS'[Month Year]=MAX('full updated ALL PARTS'[Month Year])
 )
 ), 
 CALCULATE(
 AVERAGE('full updated ALL PARTS'[Final Run Time]),
 FILTER('full updated ALL PARTS','full updated ALL PARTS'[Month Year]=MIN('full updated ALL PARTS'[Month Year])))
,0) 
- 1

Can anyone please show me how I can add 1 more criterion to the above measure to get it response to another date slicer that is 'full updated ALL PARTS'[Posting Date]? (Posting Date is a column)

 

 

Calculated Column 'full updated ALL PARTS'[Month Year]:

Month Year = FORMAT([Posting Date],"mmmm yyyy")

I tried to add the logical operator OR but it returned error.

 

 

Is there any other way to nest 2 criteria in this measure to calculate % change MoM dynamically?

 

Thank you so much!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Help with DAX to add 1 more criterion to measure

Hi @trdoan,

 

What about changing your formula like below?

% Change MoM in AVG TAT =
DIVIDE (
    CALCULATE (
        AVERAGE ( 'full updated ALL PARTS'[Final Run Time] ),
        FILTER (
            ALLSELECTED ( 'full updated ALL PARTS' ),
            'full updated ALL PARTS'[Month Year]
                = MAX ( 'full updated ALL PARTS'[Month Year] )
        )
    ),
    CALCULATE (
        AVERAGE ( 'full updated ALL PARTS'[Final Run Time] ),
        FILTER (
            ALLSELECTED ( 'full updated ALL PARTS' ),
            'full updated ALL PARTS'[Month Year]
                = MIN ( 'full updated ALL PARTS'[Month Year] )
        )
    ),
    0
)
    - 1

 

If you still need help, please share some data sample which could reproduce your scenario and your desired output so that I can understand your scenario better and get the solution.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Help with DAX to add 1 more criterion to measure

Hi @trdoan,

 

What about changing your formula like below?

% Change MoM in AVG TAT =
DIVIDE (
    CALCULATE (
        AVERAGE ( 'full updated ALL PARTS'[Final Run Time] ),
        FILTER (
            ALLSELECTED ( 'full updated ALL PARTS' ),
            'full updated ALL PARTS'[Month Year]
                = MAX ( 'full updated ALL PARTS'[Month Year] )
        )
    ),
    CALCULATE (
        AVERAGE ( 'full updated ALL PARTS'[Final Run Time] ),
        FILTER (
            ALLSELECTED ( 'full updated ALL PARTS' ),
            'full updated ALL PARTS'[Month Year]
                = MIN ( 'full updated ALL PARTS'[Month Year] )
        )
    ),
    0
)
    - 1

 

If you still need help, please share some data sample which could reproduce your scenario and your desired output so that I can understand your scenario better and get the solution.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trdoan Member
Member

Re: Help with DAX to add 1 more criterion to measure

Hi @v-piga-msft,

 

Thank you thank you thank you! It worked perfectly!

 

trdoan Member
Member

Re: Help with DAX to add 1 more criterion to measure

Hi @v-piga-msft,

 

With the formula you corrected, do you know how I can switch it to only calculate % Change between 2 specific values?

 

Let's say I have a column in the same table called 'full updated ALL PARTS'[Vendor Name].

 

Is it possible to only calculate % change between Vendor A & Vendor B, ignoring all other vendors in that column?

 

Thanks a lot!