Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HemanthV
Helper II
Helper II

Using "And" condition for same column

Hi Guys,

 

Below is my data

ID     Name Plant     Status

1AabcApproved
1AdefPendingReview
1AghiApprove - ACD
1AjklDraft
1AadfRejected
1AalkRejected
2BabcApproved
2BdefApproved
2BghiApprove - ACD
2BjklApprove - ACD
2BadfApprove - ACD
2BalkApprove - ACD
3CabcNULL
3CdefNULL
3CghiNULL
3CjklDraft
3CadfDraft
3CalkDraft
4DabcPendingReview
4DdefPendingReview
4DghiPendingReview
4DjklPendingReview
4DadfPendingReview
4DalkPendingReview

 

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:

HemanthV_0-1638176404292.png

 

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 

 

 

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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"

)

View solution in original post

ryan_mayu
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
ryan_mayu
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu 

 

ID1 is In Progress because it has mixed with all statuses

HemanthV_0-1638181363744.png

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
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"

)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.