Forum Discussion

shawn3474's avatar
shawn3474
Frequent Visitor
8 years ago
Solved

Have a Does Not Contain Problem.

Hi All.  I am new to PowerBI and am trying to create a dashboard and have run into a problem.

 

I have a table that lists project work with a list of teams in one column.  In another column I have a list of teams that have completed work on that project.   I am trying create a third column (Teams Work Still Remaining) in PowerBI that shows what teams still have work remaining.   I have tried using if([Teams]<>[Teams Work Completed],[Teams]) and that works for projects B and C but for anywhere where Teams Work Completed has multiple teams listed like for Project A below it does not work.

 

Project NameTeamsTeams Work CompletedTeams Work Still Remaining
AWeb
DBA
Network		Web
DBA		Network
BWindows
DBA		WindowsDBA
CUnix
DBA
Web		 Unix
DBA
Web

 

I have searched around and have not found any answers so far.  Any help is greatly appreciated.

Need Help
  • shawn3474's avatar
    shawn3474
    8 years ago

    I think I found my own answer by simply changing the way the data is received.  It is a slight change of process but will eliminate the need for creating a lot of logic.  Thanks for the help.  Basically we will record all teams that are part of the project and remove them once the work is done rather than add them when the work is done.

4 Replies

    • shawn3474's avatar
      shawn3474
      Frequent Visitor
      8 years ago

      v-chuncz-msft,

       

      Thank you for the response.  I am trying to work with that idea now but am wondering if you could expand on your thought a little more.  I think I see where you are going with this but am not 100% sure.

      • shawn3474's avatar
        shawn3474
        Frequent Visitor
        8 years ago

        I think I found my own answer by simply changing the way the data is received.  It is a slight change of process but will eliminate the need for creating a lot of logic.  Thanks for the help.  Basically we will record all teams that are part of the project and remove them once the work is done rather than add them when the work is done.

  • apo1979prio's avatar
    apo1979prio
    Icon for Helper I rankHelper I
    5 years ago
    Table.AddColumn(Step20, "VP", each if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 
        List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 3, 3)))  then "E"
    else if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 
        not(List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 3, 3))))  then "P"
    else if List.Contains({"ZTOP","ZREP","ZG2P","ZGCP"},[DOC_TYPE]) then "F"  //Frota
    else null)