cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RobotBroer
New Member

Add column based of calculation of multiple rows

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 NameMilestoneTask StatusMILESTONE REACHED
Project A1doneFALSE
Project A1in_progressFALSE
Project A2todoFALSE
Project A2doneFALSE
Project B1doneTRUE
Project B1doneTRUE
1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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:

vkalyjmsft_0-1665630391628.png

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.

 

View solution in original post

4 REPLIES 4
RobotBroer
New Member

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
Community Support
Community Support

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:

vkalyjmsft_0-1665630391628.png

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
Resolver I
Resolver I

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
Super User
Super User

Hi @RobotBroer ,

 

How about this one:

tackytechtom_0-1665602563806.png

 

 

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. See the Power Query M code below:

 

tackytechtom_1-1665602885376.png

 

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors