Forum Discussion

c_hudson's avatar
c_hudson
New Member
6 years ago
Solved

merge query with multiple conditions

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!

Need Help
  • SivaMani's avatar
    SivaMani
    6 years ago

    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
    ProjectActivity Name Activity Completed?Derived Activity Completed
    Project 1AYes1
    Project 1BYes1
    Project 1CYes1
    Project 2DYes1
    Project 2ANo-1
    Project 2BNo-1
    Project 2CYes1

     

    Hope this will resolve your problem!

     

    Appreciate your kudos!

     

    Regards,

    Siva Mani

2 Replies

  • SivaMani's avatar
    SivaMani
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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
    ProjectActivity Name Activity Completed?Derived Activity Completed
    Project 1AYes1
    Project 1BYes1
    Project 1CYes1
    Project 2DYes1
    Project 2ANo-1
    Project 2BNo-1
    Project 2CYes1

     

    Hope this will resolve your problem!

     

    Appreciate your kudos!

     

    Regards,

    Siva Mani

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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