Show status based on group of field with condition

[yusufashiq]

I want to handle a set of records say a group of records the Project and Task ID column has combination of 0 or 1 in the field of Status then the result should be classified as in-progress else if all status 1 then completed else if all status field is 0 then "not-started".
How to do either by DAX or Power query. Tried with DAX but taking too long to calculate and show the results.

[v-echaithra]

Hi @yusufashiq ,

Thank you @Royel , @Shahid12523 , @pankajnamekar25  for providing the inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[Royel]

Hi @yusufashiq I think we can solve this using power query and it will be optimal solution rather than dax 

Try this 

let
    Source = YourTableName,
    
    // Group by Project and Task ID, getting min and max status values
    GroupedData = Table.Group(Source, {"Project", "Task ID"}, {
        {"MinStatus", each List.Min([Status]), Int64.Type},
        {"MaxStatus", each List.Max([Status]), Int64.Type},
        {"AllRows", each _, type table}  // Keep original rows if needed
    }),
    
    // Add the status classification
    AddGroupStatus = Table.AddColumn(GroupedData, "GroupStatus", each
        if [MinStatus] = 1 and [MaxStatus] = 1 then "Completed"
        else if [MinStatus] = 0 and [MaxStatus] = 0 then "Not Started"
        else "In Progress"
    )
in
    AddGroupStatus

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[yusufashiq]

Dear, thanks for your support, I tried to do your guidence and found it takes too much time similar to DAX, the total number of records approx 37,000,000. It take to any refresh or changes I need to wait for morethan 3 to 4 hours. Is there is any other way that we can do it?

But your suggested solution is working perfectly, maybe it is good for small number of records.

[yusufashiq]

Dear, thanks for your support, I tried to do your guidence and found it takes too much time similar to DAX, the total number of records approx 37,000,000. It take to any refresh or changes I need to wait for morethan 3 to 4 hours also takes more memory. Is there is any other way that we can do it?

But your suggested solution is working perfectly, maybe it is good for small number of records.

[Royel]

@yusufashiq here is an improvised version, try this it will take less time

let
    Source = YourTableName,
    
    // Buffer the source table for better performance
    BufferedSource = Table.Buffer(Source),
    
    // Group by Project and Task ID with buffered data
    GroupedData = Table.Group(BufferedSource, {"Project", "Task ID"}, {
        {"MinStatus", each List.Min([Status]), Int64.Type},
        {"MaxStatus", each List.Max([Status]), Int64.Type}
    }),
    
    // Add the status classification
    AddGroupStatus = Table.AddColumn(GroupedData, "GroupStatus", each
        if [MinStatus] = 1 and [MaxStatus] = 1 then "Completed"
        else if [MinStatus] = 0 and [MaxStatus] = 0 then "Not Started"
        else "In Progress"
    )
in
    AddGroupStatus

Note: The data load and processing time in Power Query depends on your laptop’s performance and internet speed. If your .pbix file size is within an acceptable range, the first load may take some time. You can reduce this by using a faster laptop and a better internet connection. Once the report is published, it will run smoothly. 

[Shahid12523]

Using This

GroupStatus =
VAR TotalRows = COUNTROWS(YourTable)
VAR Count1 = CALCULATE(COUNTROWS(YourTable), YourTable[Status] = 1)
VAR Count0 = CALCULATE(COUNTROWS(YourTable), YourTable[Status] = 0)
RETURN
SWITCH(TRUE(),
Count1 = TotalRows, "Completed",
Count0 = TotalRows, "Not Started",
"In Progress"
)

[yusufashiq]

Error: Cannot convert value 'Project_1' of type Text to type True/False.

Need to make criteria with Project ID, Task ID and Process Name.

[Shahid12523]

- Group the data by Project and Task ID.
- Aggregate the Status column to get:
- Count of 1s
- Count of 0s
- Total count
- Add a custom column to classify the status.

Step-by-Step in Power Query Editor
let
Source = YourTableName,
Grouped = Table.Group(Source, {"Project", "Task ID"}, {
{"AllRows", each _, type table},
{"Count_1", each List.Count(List.Select([Status], each _ = 1)), Int64.Type},
{"Count_0", each List.Count(List.Select([Status], each _ = 0)), Int64.Type},
{"Total", each Table.RowCount(_), Int64.Type}
}),
AddStatus = Table.AddColumn(Grouped, "GroupStatus", each
if [Count_1] = [Total] then "Completed"
else if [Count_0] = [Total] then "Not Started"
else "In Progress"
)
in
AddStatus

[pankajnamekar25 · Accepted solution]

Hello @yusufashiq 

DAX Solution calculated column

ProjectStatus =
VAR MinStatus =
CALCULATE ( MIN ( Tasks[Status] ), ALLEXCEPT ( Tasks, Tasks[Project], Tasks[TaskID] ) )
VAR MaxStatus =
CALCULATE ( MAX ( Tasks[Status] ), ALLEXCEPT ( Tasks, Tasks[Project], Tasks[TaskID] ) )
RETURN
SWITCH (
TRUE(),
MinStatus = 1 && MaxStatus = 1, "Completed",
MinStatus = 0 && MaxStatus = 0, "Not Started",
"In Progress"
)

Power Query custome column

= if [Min Status] = 1 and [Max Status] = 1 then "Completed"
else if [Min Status] = 0 and [Max Status] = 0 then "Not Started"
else "In Progress"

I will suggest to go with Power Query

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn