merge query with multiple conditions

[c_hudson]

I've been beating my head against the wall all day on this, hoping someone can assist.  I have a list of Projects, each with a variable amount of Activities, and a yes/no value if the Activity is completed.  I want to consolidate the list into a single row for each Project and return a 'yes' value if all the Activities are completed, or a 'no' value if not all of the Activities are completed.  So something like this

My dataset has aound 300 projects, 18,000 activities, and is growing.  Thanks!

[amitchandak]

Try.

If will work for view by project. But for GT , you might have to use summarize

if((
count(project[Activity Name])-
countx(filter(project,project[Activity completed]="Y"),project[Activity Name]))>0,"No","Yes")

calculate(Countx(filter(summarize(project,project[project ID],"_tot",count(project[Activity Name]),
"_ctot",countx(filter(project,project[Activity completed]="Y"),project[Activity Name])),([_tot]-[_Ctot])>0),[_ctot]))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[SivaMani · Accepted solution]

@c_hudson,

I have an idea. Please check will it work or no

• Create a derived column from Activity Completed.
• Derived Activity Completed = IF('Table'[Completed?] = "Yes",1,-1) 
• Create a measure
• Project Completed = IF(MIN('Table'[Derived Activity Completed]) = 1,"Yes","No")
• Use the measure whereever you need project status

Project	Activity Name	Activity Completed?	Derived Activity Completed
Project 1	A	Yes	1
Project 1	B	Yes	1
Project 1	C	Yes	1
Project 2	D	Yes	1
Project 2	A	No	-1
Project 2	B	No	-1
Project 2	C	Yes	1

Hope this will resolve your problem!

Appreciate your kudos!

Regards,

Siva Mani