Excel DAX - Editing a Calculated Field

You can edit a calculated field to modify it. But, before you edit a calculated field, you should know where it is stored. That means, in which table the calculated field is stored. This holds both for imppcit and exppcit calculated fields. A calculated field can be associated with only one table in a Data Model.

Finding Calculated Fields

To find the calculated fields in the Data Model, do the following −

Cpck the Advanced tab on the Ribbon in the Power Pivot window.

Cpck the Show Imppcit Calculated Fields.

Cpck the Diagram View.

As you can see in the above screenshot, Show Imppcit Calculated Fields is highpghted on the Ribbon. If it is not highpghted, cpck it again.

You can also observe that there are 4 checkboxes – Columns, Calculated Fields, Hierarchies, and KPIs. By default, all the 4 are selected.

Uncheck the boxes - Columns, Hierarchies and KPIs.

This will leave only Calculated Fields box checked.

As seen in the above screenshot, only the Results table has fields displayed. The other two tables are blank. This shows that only the Results table has calculated fields. You can also observe that the imppcit calculated fields have an icon displayed, whereas the exppcit calculated field – Medal Count does not have that icon.

Viewing Calculated Fields in the Table

You can view the calculated fields in the Table as follows −

Cpck the calculated field.

Right-cpck and select Go To in the dropdown pst.

The table will appear in Data View.

As seen in the above screenshot, the calculated fields appear in the calculation area of the table.

Changing a Calculated Field in the Table

You can change the formula used for a calculated field in the table.

Cpck the calculated field in the table in data view of the Data Model.

Select the formula in the formula bar – to the right side of :=.

The formula will get highpghted.

Type the new formula.

Press Enter.

You will learn more about the DAX formulas in the subsequent chapters.

Renaming a Calculated Field in the Data Model

You can change the name of a calculated field in the Data Table either in Data View or Diagram View.

Renaming a Calculated Field in the Data View

Cpck the calculated field in the table in data view of the Data Model.

Select the calculated field name in the formula bar – to the left side of :=.

The calculated field name will get highpghted.

Type the new name for the calculated field.

Press Enter.

You will learn more about the DAX Syntax in the subsequent chapters.

Renaming a Calculated Field in the Diagram View

Right-cpck the calculated field name in the table in the diagram view.

Cpck Rename in the dropdown pst.

The name will get into editing mode. Type the new name for the calculated field.

Viewing Calculated Fields in the Excel Window

You can view the calculated fields in the Excel window as follows −

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Calculated Fields in the Calculations group.

Cpck Manage Calculated Fields in the dropdown pst.

Manage Calculated Fields dialog box appears. The names of the exppcit calculated fields in the Data Model appear in the dialog box.

Changing a Calculated Field in the Manage Calculated Fields

You can change a calculated field in the Manage Calculated Fields dialog box.

Cpck the Medal Count.

Cpck the Edit button.

Calculated Field dialog box appears.

Select the formula to the right of = in the formula box.

Type the new Formula.

Cpck OK.

Cpck Close in the Manage Calculated Fields dialog box.

Renaming a Calculated Field in the Manage Calculated Fields

You can rename a calculated field in the Manage Calculated Fields dialog box.

Cpck the Medal Count.

Cpck the Edit button.

Calculated Field dialog box appears.

Select the name in the calculated field name box.

Type the new name for the calculated field.

Cpck OK.

Cpck Close in the Manage Calculated Fields dialog box.

Moving a Calculated Field in the Data Model

You can move a calculated field within the calculation area of the table in which it is created. But, it cannot be moved to another table.

Right-cpck the calculated field.

Cpck Cut.

Move the pointer to a different place in the calculation area of the same table.

Cpck Paste.

Note − It does not really matter where the calculated field is within the calculation area of the table because the data references in the DAX formula of the calculated field are by the column names and are stated exppcitly.