Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Hadill
Helper I
Helper I

New Dependent Column

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: 

 

ObjectiveActivitiesActivities statusObjective Status
Obj AActivity 1CompletedPartially Completed
Obj AActivity 2CompletedPartially Completed
Obj AActivity 3Partially CompletedPartially Completed
Obj BActivity 1CompletedPartially Completed
Obj BActivity 2Not StartedPartially 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,

2 REPLIES 2
v-tianyich-msft
Community Support
Community Support

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:

vtianyichmsft_0-1698394648054.png

 

vtianyichmsft_1-1698394648056.png

 

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

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.