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 th...
  • 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

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