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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
trdoan
Helper III
Helper III

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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!

 

Hi @v-piga-msft,

 

Thank you thank you thank you! It worked perfectly!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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