Forum Discussion

jalaomar's avatar
jalaomar
Icon for Helper IV rankHelper IV
5 years ago
Solved

If then else statement

Hi all,

 

in need for some help and hopefully someone can support 🙂

 

currently i have two columns, ActualDate & ScheduleStart Date 

 

in my Matrix table i would like create a measure to display for example 

Contract --> if ActualDate is availble display this date but else display ScheduleStartDate or blank if no data availble in the two columns 

 

Does someone know how this measure could be stated?

 

THANKS!

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    jalaomar , Create a column like this and use

     

    coalesce([ActualDate],[ScheduleStartDate])

     

    or a measure

     

    maxx(table, coalesce(table[ActualDate],table[ScheduleStartDate]))

  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi jalaomar ,

     

    Try the following formula:

    Measure = 
SWITCH(
    MAX('Table'[BaselinePurpose]),
    "Contract", MAXX('Table', coalesce('Table'[ActualStart],'Table'[ScheduleStart])),
    "Current", MAX('Table'[ActualStart]),
    "ActualSchedule", MAX('Table'[ActualStart])
)

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi jalaomar ,

     

    Try the following formula:

    Measure_Date = 
IF(
    MAX('Table'[Actual Start]) <> BLANK(),
    MAX('Table'[Actual Start]),
    MAX('Table'[Schedule Start])
)
    Actual/Schedule vs Contr = 
DATEDIFF(
    MAXX(
        FILTER('Table','Table'[BaselinePurpose] = "Contract"),
        _Measure[Measure_Date]
    ),
    MAXX(
        FILTER('Table','Table'[BaselinePurpose] = "ActualSchedule"),
        '_Measure'[Measure_Date]
    ),
    DAY
)
    Actual/Schedule vs Curr = 
DATEDIFF(
    MAXX(
        FILTER('Table','Table'[BaselinePurpose] = "Current"),
        _Measure[Measure_Date]
    ),
    MAXX(
        FILTER('Table','Table'[BaselinePurpose] = "ActualSchedule"),
        _Measure[Measure_Date]
    ),
    DAY
)

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

     

    Best Regards,
    Winniz

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    jalaomar , Create a column like this and use

     

    coalesce([ActualDate],[ScheduleStartDate])

     

    or a measure

     

    maxx(table, coalesce(table[ActualDate],table[ScheduleStartDate]))

    • jalaomar's avatar
      jalaomar
      Icon for Helper IV rankHelper IV
      5 years ago

      Hi amitchandak, Many thanks!

       

      is it possible to put a condition to the measure?

       

      in my rows i have 

      Contract Baseline 

      Current Baseline 

      Actual/schedule 

       

      for contract baseline i would like to use the measure you created 

      for Current baseline i would like to only show actual dates according to the tollgate the project is currently in (example ITG2 and blank in the following tollgates)

      for Actual/schedule date i only want to visualize the Actual date accodring to which tollgate the projects is in currently (example ITG2)

       

      Possible to put these conditions in plase?

       

      Thanks!

       

      Best regards,

      Jala

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi jalaomar ,

         

        Try the following formula:

        Measure = 
SWITCH(
    MAX('Table'[BaselinePurpose]),
    "Contract", MAXX('Table', coalesce('Table'[ActualStart],'Table'[ScheduleStart])),
    "Current", MAX('Table'[ActualStart]),
    "ActualSchedule", MAX('Table'[ActualStart])
)

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.