Create filtered formula for each row in table based on common field

[vincenardo]

In my dataset, each row has a common field 'PhaseID', and for each 'PhaseID' there is one or more 'Process Name'(s). I need to filter the data in my matrix visual for all the 'PhaseID'(s) that contain a 'Process Name = Finishing' and show all the rows for that PhaseID where there is a Finishing Process Name. My thought was to add a formula to each row in the table to look at all the Phase ID's and if there is a 'Process = Finishing' then create a new column called 'Finishing Y/N' and return Y if it exists and N if it does not. Then I could apply a filter based on this value for each row.

My second request is that I also need to find the 'ScheduledStartDate' for the Finishing Process and create a new column called 'Finishing Start' and for each Phase ID that has a Finishing Process add this Finishing ScheduledStartDate.

As you can see in the above sample set, PhaseID 99840 has a Finishing Process Name, so the New Column 'Finishing Y/N' would have 'Y' for each row and the Finishing Start would be poplulated with the Finishing ShceduledStartDate for that same process / phase id for each row.

Any help is appreciated!

[Anonymous · Accepted solution]

Hi @vincenardo ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create two calculated columns as below:

Finishing Y/N = 
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Process Name] ),
        FILTER (
            'Table',
            'Table'[PhaseID] = EARLIER ( 'Table'[PhaseID] )
                && 'Table'[Process Name] = "Finishing"
        )
    )
RETURN
    IF ( _count >= 1, "Y", "N" )

Finishing Start = 
CALCULATE (
    MAX ( 'Table'[ScheduledStartDate] ),
    FILTER (
        'Table',
        'Table'[PhaseID] = EARLIER ( 'Table'[PhaseID] )
            && 'Table'[Process Name] = "Finishing"
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous · Accepted solution]

Hi @vincenardo ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create two calculated columns as below:

Finishing Y/N = 
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Process Name] ),
        FILTER (
            'Table',
            'Table'[PhaseID] = EARLIER ( 'Table'[PhaseID] )
                && 'Table'[Process Name] = "Finishing"
        )
    )
RETURN
    IF ( _count >= 1, "Y", "N" )

Finishing Start = 
CALCULATE (
    MAX ( 'Table'[ScheduledStartDate] ),
    FILTER (
        'Table',
        'Table'[PhaseID] = EARLIER ( 'Table'[PhaseID] )
            && 'Table'[Process Name] = "Finishing"
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[vincenardo · Accepted solution]

Work Perfectly! Thanks!