Create a calculated column from a percent column

[Anonymous]

Hi all, 

I need a formula please to create a column from another column,

Here'is my excel formula : =IFS(Q2=0%,"DRAFT",(AND(Q2>0%,Q2<100%)),"IN PROGRESS",Q2=100%,"CLOSED")

I tried this formula on power Bi : 

Status = IF('DATA'[Progress] = 0 , "DRAFT", IF('DATA'[Progress] < 100 && 'DATA'[Progress] > 0 , "IN PROGRESS" , IF('DATA'[Progress] = 100 , "CLOSED")))

But It doesn't give me the right result, for a 100% of progress, the status still in progress or it should be closed.

Thanks,

[Anonymous]

Thank you all

It worked

[v-lili6-msft · Accepted solution]

HI  @Anonymous 

Just adjust the the formula as below:

Status =
IF (
    'DATA'[Progress] = 0,
    "DRAFT",
    IF (
        'DATA'[Progress] < 1
            && 'DATA'[Progress] > 0,
        "IN PROGRESS",
        IF ( 'DATA'[Progress] = 1, "CLOSED" )
    )
)

Regards,

Lin

[TomMartens]

Hey @Anonymous ,

I use this DAX statement to create a calculated column:

Status = 
var __Progress = 'Table'[Progress]
return
SWITCH(
    TRUE() 
    , __Progress = 0 , "Draft"
    , __Progress = 1 , "Closed"
    , "In Progress"
)

As you can see, it's using a variable to avoid multiple evaluations of the Progress column. The table will look like this:

The DAX is not that different from the code you provided, you might double-check the value of the Progress column, it 75% is expressed as 0.75, this would explain why your statement returns "IN PROGRESS" instead of "CLOSED".

Regards,

Tom