Forum Discussion
Helper IIIHow to do <100% in custom column
Hi
I've tried the below, however it isnt working. The status column is text type and the % complete is % (see below screenshot). I basically want to say if the status column is "Status" and the % completed column is less than 100% then "Discrepancy"
if [Status]="Completed" and [#"% Complete"] <1 then "Discrepancy"
else if [Status]="In Progress" and [#"% Complete"]=1 then "Discrepancy"
else if [Status]="Overdue" and [#"% Complete"]=1 then "Discrepancy"
else if [Status]="Not Started" and [#"% Complete"]=1 then "Discrepancy"
else "")
thank you in advance
Sorry, I didn't include my whole formula initially. This is it below - so the ) wasn't the issue. The <1.0 worked and is bringing through the correct info now. I found the fix on another PBI post.
= Table.AddColumn(#"Added Conditional Column6", "Status completed & % complete not 100%", each if [Status]="Completed" and [#"% Complete"] <1.0 then "Discrepancy"
else if [Status]="In Progress" and [#"% Complete"]=1.0 then "Discrepancy"
else if [Status]="Overdue" and [#"% Complete"]=1.0 then "Discrepancy"
else if [Status]="Not Started" and [#"% Complete"]=1.0 then "Discrepancy"
else "")
4 Replies
- PhilipTreacy
Super User
Hi siddrow
How exactly is it not working?
The only problem I can find is that you don't need a closing parenthesis )
This code works for me
if [Status]="Completed" and [#"% Complete"] <1 then "Discrepancy" else if [Status]="In Progress" and [#"% Complete"]=1 then "Discrepancy" else if [Status]="Overdue" and [#"% Complete"]=1 then "Discrepancy" else if [Status]="Not Started" and [#"% Complete"]=1 then "Discrepancy" else ""Regards
Phil
siddrowThanks for the response Phil. I ended up working it out - I had to change the <1 to <1.0 and it fixed it
- PhilipTreacy
Hi siddrow
Changing 1 to 1.0 shouldn't have had bearing on the problem. If you check my example file you'll see the code works fine using just 1.
You sure the problem wasn't something else, like the ) at the end of the code?
Regards
Phil
