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 displ...
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

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

Hi v-kkf-msft Thanks for looking into this 

 

the measure is not displaying as i would like to but the calculation is correct.

 

for BaselinePurpose 

Contract 

Current 

Actual/schedule

I need to take into considiration that if there is a "ActualStart" date then the measure should be based on that date else take the "ScheduleStart" Date 

 

se below picture of a little sample of the dataset

I will visialize the measure in a matrix table where the

BaselinePurpose is the rows and Tollgate is the columns and the measure is the value 

Hope this made it a bit more clear, otherwise let me know

 

Many thanks!!

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
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

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

    Hi v-kkf-msft 

    much better, thanks!!

    It work as intended 🙂 

     

     

    Best regards,

    Jala