Forum Discussion

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

IF Statement

Hi

 

I am trying to create a column in my table called "Value"that looks at the number in the Actual Duration column and inputs a value based on the table below.  Please can someone help me with the the DAX coding to get his result.

 

Many thanks

 

 

Actual Duration Value
0-1515
16-2020
21-3030
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, spandy34 

    Please try following steps:

    1.add calculated columns "Start",'End' as below:

    Start = LEFT('Table'[Actual Duration], search("-",'Table'[Actual Duration])-1)
    End = RIGHT('Table'[Actual Duration],len('Table'[Actual Duration])-search("-",'Table'[Actual Duration]))

    2.Add a measure like:

    Result =
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Start] <= MAX ( 'Fact Table'[Actual Duration] )
            && 'Table'[End] >= MAX ( 'Fact Table'[Actual Duration] )
    )
)

     

    Best Regards,
    Community Support Team _ Eason

2 Replies

  • spandy34's avatar
    spandy34
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    Hi

     

    I am trying to create a column in my table called "Value"that looks at the number in the Actual Duration column and inputs a value based on the table below.  Please can someone help me with the the DAX coding to get his result.

     

    Many thanks

     

     

    Actual Duration Value
    0-1515
    16-2020
    21-3030

    amitchandak 

     

     

    • v-easonf-msft's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, spandy34 

      Please try following steps:

      1.add calculated columns "Start",'End' as below:

      Start = LEFT('Table'[Actual Duration], search("-",'Table'[Actual Duration])-1)
      End = RIGHT('Table'[Actual Duration],len('Table'[Actual Duration])-search("-",'Table'[Actual Duration]))

      2.Add a measure like:

      Result =
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Start] <= MAX ( 'Fact Table'[Actual Duration] )
            && 'Table'[End] >= MAX ( 'Fact Table'[Actual Duration] )
    )
)

       

      Best Regards,
      Community Support Team _ Eason