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 ID Predecessors C B which is same Acti...
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.

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: