Forum Discussion
Using "And" condition for same column
Hi Guys,
Below is my data
ID Name Plant Status
| 1 | A | abc | Approved |
| 1 | A | def | PendingReview |
| 1 | A | ghi | Approve - ACD |
| 1 | A | jkl | Draft |
| 1 | A | adf | Rejected |
| 1 | A | alk | Rejected |
| 2 | B | abc | Approved |
| 2 | B | def | Approved |
| 2 | B | ghi | Approve - ACD |
| 2 | B | jkl | Approve - ACD |
| 2 | B | adf | Approve - ACD |
| 2 | B | alk | Approve - ACD |
| 3 | C | abc | NULL |
| 3 | C | def | NULL |
| 3 | C | ghi | NULL |
| 3 | C | jkl | Draft |
| 3 | C | adf | Draft |
| 3 | C | alk | Draft |
| 4 | D | abc | PendingReview |
| 4 | D | def | PendingReview |
| 4 | D | ghi | PendingReview |
| 4 | D | jkl | PendingReview |
| 4 | D | adf | PendingReview |
| 4 | D | alk | PendingReview |
Now i am trying to create a new column with some conditions. Below are 4 conditions for new column
If a ID has:
Status = "NULL" and "DRAFT" or any of them, then value will be "Yet To Start"
Status = "Approved" (or) "Approve - ACD/Rejected" (or) Both then value will be "Completed"
Status = "Pending Review" then value will be "In Progress"
If status is mixed for an ID like (ID 1 for example)
"Approved" and "PendingReview" and "Draft" and "NULL" then value will be "In Progress"
Expected Result:
When i tried to use && Symbol for conditions, i am getting blank values
Note: I need this in column instead of measure because i will use same column for legend in donut chart.
Attaching sample pbix for referrence
https://drive.google.com/file/d/1WX-qYpr_R4Xg1VKjlo6wdmAEa3Zzp_NC/view?usp=sharing
Please help me on this!
Thanks in Advance!
parry2k amitchandak Jihwan_Kim
HemanthV , Try a new column like example
New column =
var _1 = countx(filter(Table, [ID] = earlier([ID]) && [Status]= "NULL"),[Status])
var _2 = countx(filter(Table, [ID] = earlier([ID]) && [Status]= "Draft"),[Status])
var _3 = countx(filter(Table, [ID] = earlier([ID]) && [Status] in {"Approved", "Approve - ACD/Rejected"}),[Status])
var _4 = countx(filter(Table, [ID] = earlier([ID]) && [Status] in {"Pending Review"}),[Status])
return
Switch ( True() ,
not(isblank(_1)) && not(isblank(_2)) , "Yet To Start",
not(isblank(_3)) , "Completed" ,
not(isblank(_4)) ,"In Progress"
)
why ID 1 is in progress, I didn't see NULL of ID 1.
maybe you can try this
Column = VAR _null=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="NULL"),Table1[Status]) var _draft=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="Draft"),Table1[Status]) var _approve=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="Approved"),Table1[Status]) var _acd=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="Approve - ACD"),Table1[Status]) var _pending=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="PendingReview"),Table1[Status]) return if(_approve<>""&&_pending<>""&&_null<>""&&_draft<>"","In progess",if(_null<>""||_draft<>"","Yet to start",if(_approve<>""||_acd<>"","Completed",if(_pending<>"","In progress"))))
6 Replies
HemanthV , Try a new column like example
New column =
var _1 = countx(filter(Table, [ID] = earlier([ID]) && [Status]= "NULL"),[Status])
var _2 = countx(filter(Table, [ID] = earlier([ID]) && [Status]= "Draft"),[Status])
var _3 = countx(filter(Table, [ID] = earlier([ID]) && [Status] in {"Approved", "Approve - ACD/Rejected"}),[Status])
var _4 = countx(filter(Table, [ID] = earlier([ID]) && [Status] in {"Pending Review"}),[Status])
return
Switch ( True() ,
not(isblank(_1)) && not(isblank(_2)) , "Yet To Start",
not(isblank(_3)) , "Completed" ,
not(isblank(_4)) ,"In Progress"
)
why ID 1 is in progress, I didn't see NULL of ID 1.
maybe you can try this
Column = VAR _null=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="NULL"),Table1[Status]) var _draft=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="Draft"),Table1[Status]) var _approve=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="Approved"),Table1[Status]) var _acd=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="Approve - ACD"),Table1[Status]) var _pending=maxx(FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])&&Table1[Status]="PendingReview"),Table1[Status]) return if(_approve<>""&&_pending<>""&&_null<>""&&_draft<>"","In progess",if(_null<>""||_draft<>"","Yet to start",if(_approve<>""||_acd<>"","Completed",if(_pending<>"","In progress"))))
