Forum Discussion

lmperplies's avatar
lmperplies
Regular Visitor
3 years ago
Solved

How can I pull data based on a predecessor?

I have a table where the key is the Activity ID. This Activity ID is used to identity predecessors for a specific Activity ID.

 

For example

Activity IDPredecessors
CB which is same Activity ID as the table below
CA which is same Activity ID as the table below

 

What I am trying to do is have a slicer for the Activity ID, that will allow me to display the records for  predecessor B and A for the user.

 

Here is an Example of the main table

Activity IDStart DateFinish DateActivity Status
C1/1/20201/2/2020Completed
B3/4/20215/6/2021Completed
A5/2/2029 Not started

 

Here is what I am trying to create

Slicer

Activity ID (user enters)
C

 

Visual like a table

Activity IDPrecessorStart DateFinish DateActivity Status
CB3/4/20215/6/2021Completed
CA5/2/2029 Not started

 

How can I do this?

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi lmperplies ,

    According to your description, I create a sample.

    Activity table:

    Predecessors table:

    The two tables are related with Activity ID column.

    Here's my solution, create three measures.

    StartDate =
MAXX (
    FILTER (
        ALL ( 'Activity' ),
        'Activity'[Activity ID] = MAX ( 'Predecessors'[Predecessors] )
    ),
    'Activity'[Start Date]
)

    FinishDate =
MAXX (
    FILTER (
        ALL ( 'Activity' ),
        'Activity'[Activity ID] = MAX ( 'Predecessors'[Predecessors] )
    ),
    'Activity'[Finish Date]
)

    ActivityStatus =
MAXX (
    FILTER (
        ALL ( 'Activity' ),
        'Activity'[Activity ID] = MAX ( 'Predecessors'[Predecessors] )
    ),
    'Activity'[Activity Status]
)

    Then put Activity ID column from Activity table into a slicer, put Activity ID and Predecessors columns from Predecessors table and the three measures into a visual, get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

4 Replies

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    3 years ago

    Hi, 

    Not sure, if it is useful for you:

    Create a table just including the ActivityId  :

    Sheet165NewActivity = values(Sheet165[Activity ID])
    Then in the main table create the measure as below :
    ExcluseActivity =
    Var _SelectCnt = COUNT(Sheet165NewActivity[Activity ID])
    Var _SelectIn = if( max(Sheet165[Activity ID]) in ALLSELECTED(Sheet165NewActivity[Activity ID]),1,0)
    Return if(_SelectCnt <> 3,
              if( max(Sheet165[Activity ID]) in ALLSELECTED(Sheet165NewActivity[Activity ID]),
                  1,
                  0),
              0
            )
    Then develop the slicer on the new table and ActivityId column, and in the main visual put the filter ExcludeActivity is 0

    As you see if each activityId is selected the other is shown an if nothing is selected the all value is shown :

    Appreciate you Kudos

    • lmperplies's avatar
      lmperplies
      Regular Visitor
      3 years ago

      I was able to build the new table no problem. However, my data did not enter any values in the table that I used the "ExcluseActivty" measure as a filter. I am wondering if you could share with me how you set up the Sheet165 (main table) connecting Activity C to Activity B & A as predecessors. 

       

      For activity 35m4w.10, the predecessors should be 35m4w.06 and 35m4w.07, but nothing pulls.

      Here is what I am returning:

       

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi lmperplies ,

    According to your description, I create a sample.

    Activity table:

    Predecessors table:

    The two tables are related with Activity ID column.

    Here's my solution, create three measures.

    StartDate =
MAXX (
    FILTER (
        ALL ( 'Activity' ),
        'Activity'[Activity ID] = MAX ( 'Predecessors'[Predecessors] )
    ),
    'Activity'[Start Date]
)

    FinishDate =
MAXX (
    FILTER (
        ALL ( 'Activity' ),
        'Activity'[Activity ID] = MAX ( 'Predecessors'[Predecessors] )
    ),
    'Activity'[Finish Date]
)

    ActivityStatus =
MAXX (
    FILTER (
        ALL ( 'Activity' ),
        'Activity'[Activity ID] = MAX ( 'Predecessors'[Predecessors] )
    ),
    'Activity'[Activity Status]
)

    Then put Activity ID column from Activity table into a slicer, put Activity ID and Predecessors columns from Predecessors table and the three measures into a visual, get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

    • lmperplies's avatar
      lmperplies
      Regular Visitor
      3 years ago

      Thank you so much. I was able to get this work.