Forum Discussion

TTKellogg's avatar
TTKellogg
New Member
5 years ago
Solved

Analyze Data based on Multiple Criteria

Hi.  I really need some help!  I have a list with multiple projects in it (Proj_Short_Name).  Within each project there are task_code.  The task_code are unique within a project but are reused across multiple projects.  I need to be able to flag Task_Codes that are missing a specific Predecessor_Relationship_Type or a specific Successor_Relationship_Type.  So within a Project_Short_Name I need to be able to look at the Task_Code and if the task code does not have aPredecessor_Relationship_Type that is a Finish to Start relationship or a Start to Start relationship then it is flagged.  I am not sure if I should somehow make a list where is the project short name and task code are the same, then combine the Predecessor Relationship Types into one list then I can flag it that way? In the sample below, the Task Code AMP.KSK.360 should not be flagged because it does have an existing Finish to Start relationship.  I am just not sure how to check for multiple rows and if its the same project and the same activity then check all of its relationships before flagging.

 

 

 

Need Help
Tutorial Requests
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Taffalaffa ,

     

    Use the following two measures:

     

     

    Pred Dangler = 
VAR A = { "Finish to Start", "Start to Start" }
VAR B =
    CALCULATETABLE (
        VALUES ( 'Starting Data'[PRED_REL_TYPE]),
        FILTER (
           ALL( 'Starting Data'),
            'Starting Data'[PROJ_SHORT_NAME] = EARLIER( 'Starting Data'[PROJ_SHORT_NAME] )
                && 'Starting Data'[TASK_CODE] = EARLIER ( 'Starting Data'[TASK_CODE] )
        )
    )
RETURN
    IF ( COUNTROWS ( EXCEPT ( A, B ) ) > 0, 0, 1 )


Succ Dangler = 
VAR A = { "Finish to Start", "Finish to Finish" }
VAR B =
    CALCULATETABLE (
        VALUES ( 'Starting Data'[PRED_REL_TYPE] ),
        FILTER (
            ALL('Starting Data'),
            'Starting Data'[PROJ_SHORT_NAME] = EARLIER ( 'Starting Data'[PROJ_SHORT_NAME] )
                && 'Starting Data'[TASK_CODE] = EARLIER ( 'Starting Data'[Task_Code] )
        )
    )
RETURN
    IF ( COUNTROWS ( EXCEPT ( A, B ) ) > 0, 0, 1 )

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

4 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi TTKellogg ,

     

    You can use the following two calculated column:

     

    Pred Dangler =
VAR A = { "Finish to Start", "Start to Start" }
VAR B =
    CALCULATETABLE (
        VALUES ( Table[PRED_REL_TYPE] ),
        FILTER (
            Table,
            Table[PROJ_SHORT_NAME] = MAX ( Table[PROJ_SHORT_NAME] )
                && Table[TASK_CODE] = MAX ( Table[TASK_CODE] )
        )
    )
RETURN
    IF ( COUNTROWS ( EXCEPT ( A, B ) ) > 0, 0, 1 )



Succ Dangler =
VAR A = { "Finish to Start", "Finish to Finish" }
VAR B =
    CALCULATETABLE (
        VALUES ( Table[SUCC_REL_TYPE] ),
        FILTER (
            Table,
            Table[PROJ_SHORT_NAME] = MAX ( Table[PROJ_SHORT_NAME] )
                && Table[TASK_CODE] = MAX ( Table[TASK_CODE] )
        )
    )
RETURN
    IF ( COUNTROWS ( EXCEPT ( A, B ) ) > 0, 0, 1 )

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

    • Taffalaffa's avatar
      Taffalaffa
      Helper I
      5 years ago

      Hi Dedmon,

      Thank you for the reply.  I think that this is super close but I get an error with your code.  It is saying there is a cirrcular dependency but I am not sure why...

      Danglers.pbix 

       

       

       

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi Taffalaffa ,

         

        Use the following two measures:

         

         

        Pred Dangler = 
VAR A = { "Finish to Start", "Start to Start" }
VAR B =
    CALCULATETABLE (
        VALUES ( 'Starting Data'[PRED_REL_TYPE]),
        FILTER (
           ALL( 'Starting Data'),
            'Starting Data'[PROJ_SHORT_NAME] = EARLIER( 'Starting Data'[PROJ_SHORT_NAME] )
                && 'Starting Data'[TASK_CODE] = EARLIER ( 'Starting Data'[TASK_CODE] )
        )
    )
RETURN
    IF ( COUNTROWS ( EXCEPT ( A, B ) ) > 0, 0, 1 )


Succ Dangler = 
VAR A = { "Finish to Start", "Finish to Finish" }
VAR B =
    CALCULATETABLE (
        VALUES ( 'Starting Data'[PRED_REL_TYPE] ),
        FILTER (
            ALL('Starting Data'),
            'Starting Data'[PROJ_SHORT_NAME] = EARLIER ( 'Starting Data'[PROJ_SHORT_NAME] )
                && 'Starting Data'[TASK_CODE] = EARLIER ( 'Starting Data'[Task_Code] )
        )
    )
RETURN
    IF ( COUNTROWS ( EXCEPT ( A, B ) ) > 0, 0, 1 )

         

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

        Best Regards,

        Dedmon Dai