Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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"
)
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"))))
Proud to be a Super User!
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"))))
Proud to be a Super User!
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
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?
Proud to be a Super User!
yes, thank you so much for helping me out! I tweaked the conditions you sent and got desired result.
Thank you so much!
you are welcome
Proud to be a Super User!
@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"
)
User | Count |
---|---|
136 | |
73 | |
72 | |
56 | |
55 |
User | Count |
---|---|
199 | |
95 | |
63 | |
62 | |
51 |