Using "And" condition for same column

[HemanthV]

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 

[amitchandak · Accepted solution]

@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"

)

[ryan_mayu · Accepted solution]

@HemanthV 

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"))))

[HemanthV]

@ryan_mayu 

ID1 is In Progress because it has mixed with all statuses

In sample data it doesn't have null but it has remaining 3 conditions

[ryan_mayu]

@HemanthV 

your description lists 4 status, then the result is in progress. So if an ID has any three conditions, then the result is in progress?

[HemanthV]

@ryan_mayu 

yes, thank you so much for helping me out! I tweaked the conditions you sent and got desired result.

Thank you so much!

[ryan_mayu]

you are welcome

[amitchandak · Accepted solution]

@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"

)