Forum Discussion

amandabus21's avatar
amandabus21
Helper V
3 years ago
Solved

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 =      ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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]
    )
    )

amandabus21's avatar
amandabus21
Helper V
3 years ago

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"

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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]
)
)

  • amandabus21's avatar
    amandabus21
    Helper V
    3 years ago

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

  • amandabus21's avatar
    amandabus21
    Helper V
    3 years ago

    This gave blank values. 

     

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

     

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    amandabus21 

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

  • amandabus21's avatar
    amandabus21
    Helper V
    3 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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]
    )
    )

  • amandabus21's avatar
    amandabus21
    Helper V
    3 years ago

    Giving me the same values as column Variable Differennce 

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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]
    )
    )