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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
amandabus21
Helper V
Helper V

COnditonal Column

How can I change this formula so that it only calculates if the previous Equipment Key does not equal the next Equipment Key. 

It then should be 0.

 

Variable Difference =
    VAR curr =
        CALCULATE ( MAX ( 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]) )
    VAR prev =
        MAXX (
            FILTER (
                'PM On Time Performance Bus PM-BI-002 (2)',
                'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] = EARLIER ( 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] )
                    && 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] < EARLIER ('PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] )
            ),
    [Mileage]
        )
    RETURN
        curr - prev
1 ACCEPTED SOLUTION

@amandabus21 

Please try

MeasureDifference =
VAR FirstSelectedDate =
CALCULATE (
MIN ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] ),
ALLSELECTED (),
VALUES ( 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] )
)
RETURN
SUMX (
'PM On Time Performance Bus PM-BI-002 (2)',
IF (
'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] = FirstSelectedDate,
0,
'PM On Time Performance Bus PM-BI-002 (2)'[Variable Difference]
)
)

View solution in original post

14 REPLIES 14
tamerj1
Super User
Super User

HI @amandabus21 
Please try

Variable Difference =
VAR CurrentSort =
    'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] * 1000000
        + INT ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] )
VAR Curr = 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]
VAR T =
    ADDCOLUMNS (
        'PM On Time Performance Bus PM-BI-002 (2)',
        "@Sort",
            'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] * 1000000
                + INT ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] )
    )
VAR Prev =
    MAXX ( FILTER ( T, [@Sort] < CurrentSort ), [Mileage] )
RETURN
    Curr - Prev

@tamerj1 thank you, but not quite what I was looking for. 

 

For example, for Equipment key 1104, it should just "Else=0" because there is no previous equipment key record for that equipment. In Equipment 1106 it should calculate the "current - previous" mileage because there is a previous entry. 

 

amandabus21_0-1677532969763.png

 

What it should look like:

amandabus21_1-1677533037821.pngamandabus21_2-1677533056940.png

 

@amandabus21 
Perhaps I misunderstood your requirement. I'm still not sure if I fully understand 🙂

Please try

Variable Difference =
VAR CurrentDate = 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01]
VAR Curr = 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]
VAR CurrentEquipmentTable =
    CALCULATETABLE (
        'PM On Time Performance Bus PM-BI-002 (2)',
        ALLEXCEPT (
            'PM On Time Performance Bus PM-BI-002 (2)',
            'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key]
        )
    )
VAR TableBefore =
    FILTER (
        CurrentEquipmentTable,
        'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] < CurrentDate
    )
VAR PreviousRecord =
    TOPN (
        1,
        TableBefore,
        'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01]
    )
VAR Prev =
    MAXX ( PreviousRecord, 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage] )
RETURN
    Curr - COALESCE ( Prev, Curr )

@tamerj1 yes this is almost exactly what I need! the formula is working great, just need to add the contional rule on top of it.

so, if there is there is no previous matyching equipment ID the Variable Difference formula should equal 0.

 

if there is a previous equipment ID, the formula needs to run. 

 

amandabus21_0-1677599358806.png

 

In this example, the equipment key = 1104, variable difference should yield a "0" because there is no other previous record of 1104.

 

But the  second record of1105 is correct, because there is a previous record.

 

@amandabus21 

Strange! It should return 0. Actually this is the purpose of the COALESCE. Would you please share the code that you have used?

@tamerj1 I used the one you provided above.

 

Only other thing I could maybe think of is a Date Slicer added using, "Completion Date.Completion Date Level 01"

@amandabus21 

Please try adding a new measure 

MeasureDifference =
SUMX (
'PM On Time Performance Bus PM-BI-002 (2)',
IF (
'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01]
= CALCULATE (
MIN ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] ),
REMOVEFILTERS (),
VALUES ( 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] )
),
'PM On Time Performance Bus PM-BI-002 (2)'[Variable Difference]
)
)

This gave blank values. 

 

If i remove ""Sum" from Variable difference, the whole table goes blank.

 

amandabus21_0-1677606452498.png

 

@amandabus21 

Sorry my mistake. I'm typing on the phone so please forgive such mistakes 

SUMX (
'PM On Time Performance Bus PM-BI-002 (2)',
IF (
'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01]
= CALCULATE (
MIN ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] ),
REMOVEFILTERS (),
VALUES ( 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] )
),
0,

'PM On Time Performance Bus PM-BI-002 (2)'[Variable Difference]
)
)

Giving me the same values as column Variable Differennce 

amandabus21_0-1677607478593.png

 

@amandabus21 

Please try

MeasureDifference =
VAR FirstSelectedDate =
CALCULATE (
MIN ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] ),
ALLSELECTED (),
VALUES ( 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] )
)
RETURN
SUMX (
'PM On Time Performance Bus PM-BI-002 (2)',
IF (
'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] = FirstSelectedDate,
0,
'PM On Time Performance Bus PM-BI-002 (2)'[Variable Difference]
)
)

That worked thank you sooo much for all your help! wow!

@amandabus21 

This is a new Measure which calculates based on the original Calculated Column. 

Yes, I created as a new measure and added to the table. Its still blank.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors