New Dependent Column

[Hadill]

Hi There, 

I am trying to create a new column and name it [Objective Status]. I want this column to be dependent on a group of records available in another column named [Objectives] as the following:

If the all Obj A has the same Activities Status of "Completed" or "Partially Completed" or "Not started", bring the same result. But if it has different Activities Status for example "Completed" and "Partially Completed", bring "Partially Completed" only. Just like the example below: 

Objective	Activities	Activities status	Objective Status
Obj A	Activity 1	Completed	Partially Completed
Obj A	Activity 2	Completed	Partially Completed
Obj A	Activity 3	Partially Completed	Partially Completed
Obj B	Activity 1	Completed	Partially Completed
Obj B	Activity 2	Not Started	Partially Completed

I am not sure if there is a way to get the same result with out a need to add a new DAX column. 

If I need a DAX column, what will be the appropriate formula?

Thanks a lot in advance. 

Br,

[v-tianyich-msft]

Hi @Hadill ,

It is true that there is a solution that can be applied without DAX, but it has some drawbacks, and there must be a completed state for either activity to exist and you may check the following results:

My thoughts are to use it as an identifier for each state, and then filter through the Discount to see if it's a single state, and if it's not unique then it's not fully completed, provided of course that one of your activities is completed

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Objective", type text}, {"Activities", type text}, {"Activities status", type text}}),

    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Activities"}),

    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [Activities status] = "Completed" then 0 else if [Activities status] = "Partially Completed" then 1 else if [Activities status] = "Not Started" then 2 else null),

    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Objective"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),

    #"Added Conditional Column1" = Table.AddColumn(#"Grouped Rows", "Final Status", each if [Count] = 1 then "Completed" else "Partially Completed")

in

    #"Added Conditional Column1"

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[123abc]

n Power BI, you can create a new calculated column using DAX (Data Analysis Expressions) to achieve the desired "Objective Status" based on the conditions you've described. You can create this column using Power Query in the Power BI Query Editor. Here's how you can do it:

1. Load your data into Power Query.

2. In Power Query, go to the "Model" tab.

3. Click on "New Column" to create a new calculated column.

4. Use the following DAX formula to create the "Objective Status" column:

Objective Status =
VAR ObjectiveGroup = FILTER(YourTable, YourTable[Objective] = EARLIER(YourTable[Objective]))
RETURN
IF (
COUNTROWS(ObjectiveGroup) = COUNTROWS(FILTER(ObjectiveGroup, ObjectiveGroup[Activities Status] = "Completed")) ||
COUNTROWS(ObjectiveGroup) = COUNTROWS(FILTER(ObjectiveGroup, ObjectiveGroup[Activities Status] = "Partially Completed")) ||
COUNTROWS(ObjectiveGroup) = COUNTROWS(FILTER(ObjectiveGroup, ObjectiveGroup[Activities Status] = "Not Started")),
"Partially Completed",
"Different Status"
)

Make sure to replace "YourTable" with the name of your table. This formula checks if all rows within the same Objective group have the same "Activities Status" of "Completed," "Partially Completed," or "Not Started." If they do, it assigns "Partially Completed" to the "Objective Status" column; otherwise, it assigns "Different Status."

1. Click "OK" to create the new column.

2. Close and apply the changes to your data model.

Now, you will have a new column named "Objective Status" that calculates the desired values based on your specified conditions for each objective.

Please adapt the table and column names in the DAX formula to match your actual dataset.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.