Add column based of calculation of multiple rows

[RobotBroer]

I have a table with many projects, each row representing a task of a project where each task has a milestone and a status.

I want to filter my matrix table to show only completed tasks where the whole milestone is completed.

I thought I can create a new column "Milestone Reached" which will be true if each task status within the project and milestone is "done", and false otherwise.

I can then use the column as a visual filter.

Please help me with the function to create the column "milestone reached", or suggest an alternative approach. 

*Note the visual I am using is a Matrix

Thank you

Project Name	Milestone	Task Status	MILESTONE REACHED
Project A	1	done	FALSE
Project A	1	in_progress	FALSE
Project A	2	todo	FALSE
Project A	2	done	FALSE
Project B	1	done	TRUE
Project B	1	done	TRUE

[RobotBroer]

Thank you,

I just had to add another dimension for the milestone too:

MILESTONE REACHED =
IF (
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Project Name] = EARLIER ( 'Table'[Project Name] )
             && 'Table'[Milestone] = EARLIER ( 'Table'[Milestone] )
                && 'Table'[Task Status] <> "done"
        )
    ) > 0,
    "FALSE",
    "TRUE"
)

[v-yanjiang-msft · Accepted solution]

Hi @RobotBroer ,

Here's the DAX solution. Create a calaulated column.

MILESTONE REACHED =
IF (
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Project Name] = EARLIER ( 'Table'[Project Name] )
                && 'Table'[Task Status] <> "done"
        )
    ) > 0,
    "FALSE",
    "TRUE"
)

Result:

I also 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.

[alannavarro]

Hello, hope this helps. 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRMgTilPy8VKVYHUyJzLz4gqL89KLU4mIMeSMgLslPyccqgWGiEy6rUCViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Milestone = _t, #"Task Status" = _t]),
#"Grouped Rows" = Table.Group(Source, {"Project Name"}, {{"Count", each _, type table [Project Name=nullable text, Milestone=nullable text, Task Status=nullable text]}}),
Add_Column = (check)=>
let
#"Task Status" = check[Task Status],
Custom1 = List.ContainsAll({"done"},#"Task Status")
in
Custom1,
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "Milestone reached", each Add_Column([Count])),
#"Expanded Count" = Table.ExpandTableColumn(#"Invoked Custom Function", "Count", {"Milestone", "Task Status"}, {"Milestone", "Task Status"})
in
#"Expanded Count"

[tackytechtom]

Hi @RobotBroer ,

How about this one:

The idea is the following:

1. Filter the table where task status <> done

2. Get a distinct list of all Project Names left

3. Join the initial table with the distinct list

4. Anytime you get a hit --> your Project is not yet finished

I am not sure whether this is exactly what you want since your example table indicates that a milestone for a project can be both done and in_progress (milestone 1 for project A). So I might have misunderstood your query. However, I believe the idea from above should work for other scenarios as well. Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRMgTilPy8VKVYHUyJzLz4gqL89KLU4mIMeSMgLslPyccqgWGiEy6rUCViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Milestone = _t, #"Task Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Milestone", Int64.Type}, {"Task Status", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Task Status] <> "done")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Project Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Project Name"}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project Name"}, #"Removed Duplicates", {"Project Name"}, "Removed Duplicates", JoinKind.LeftOuter),
    #"Expanded Removed Duplicates" = Table.ExpandTableColumn(#"Merged Queries", "Removed Duplicates", {"Project Name"}, {"Removed Duplicates.Project Name"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Removed Duplicates", "Custom", each if [Removed Duplicates.Project Name] <> null then false else true),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Removed Duplicates.Project Name"})
in
    #"Removed Columns"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/