Dataverse and calculated columns

Without a Dynamics 365 background, Dataverse can seem like an endless surprise. There is always something new. This time we will get to know one interesting feature, calculated columns.

There are two types of calculated columns.

• Rollup = Collection of rows related to the row. For example, how many contacts are related to this customer.
• Calculated column – Calculation performed between column values. For example, the average from columns A, B and C. You can also use the columns found behind the relation in the calculation.

This seems really convenient. Let’s get to know each other a little more.

Example – Cleaning apartments

Let’s have a solution that maintains information about the cleaning of rooms, apartments and residential buildings. In the data model, each room is connected to an apartment, which in turn is connected to a building.

Data is maintained with model-driven Power Apps.

Individual rooms are marked as cleaned with mobile Power Apps.

How many rooms in apartment x have been cleaned?

We would like to show the number of cleaned rooms per apartment in the tools. This can be easily done with a rollup column. It can

• Search for all rows related to a single row (rooms related to this apartment)
• Searched rows can be filtered (cleaned rooms only)
• Based on one column, a calculation can be performed from the resulting set (count, sum max, min, avg). We count the number of cleaned rooms (=rows)

Let’s create a new column for the apartment (Count of rooms cleaned). Its type is an integer and its behavior is defined as rollup.

After saving, the calculation rule is defined. We want to count the number of cleaned rooms related to the apartment. It is done as follows.

What percentage of the rooms in the apartment have been cleaned?

More precisely, we want to know the cleaning percentage of the rooms in the apartment. For the calculation, we need information on the number of rooms in the apartment. Let’s create another summary column (Count of rooms), whose calculation rule is a little simpler.

Finally, a third column (Cleaned %) is created, where the cleaning percentage is calculated using the values ​​of the previous columns. The column is a decimal number and its function is calculated.

We get to define the calculation rule for the column. In addition to the row data, we can use the data found behind the relations.

The column value is calculated as the percentage of cleaned rooms out of all rooms in the apartment.

Update of summary columns

Everything is ready, but the values ​​in the new columns are empty.

The reason becomes clear when we open the information of one apartment.

The values ​​of the summary columns are not calculated in real time, but in a scheduled task that runs 12 hours after the column is created. After that, they are updated once an hour.

Yes. Once in an hour.

The value can also be updated manually with the Recalculate button that appears behind the calculator.

Let’s manually update a few. Note that the calculated columns are real-time. Their values ​​are updated immediately.

But this cannot be used in the employee’s mobile application. When the room is marked as cleaned, the apartment’s cleaning percentage should increase at the same time. Otherwise, the whole field just confuses users.

Updating a summary column with Flow

Can the summary column be forced to update? If you update it from the mobile application every time the room is marked as cleaned?

The value of the summary column can be updated with an API call. But I’m not sure if it’s worth playing this game. Rather, I would solve this problem somehow differently in the mobile application.

How much of the building has been cleaned?

Now we know how many of the apartment’s rooms have been cleaned. Is it possible to create the corresponding columns for the building we are counting on

• how many rooms there are in the building
• how many of them have been cleaned
• what is the cleaning percentage of the building

This will not work. When calculating the summary column, you cannot use the calculated columns behind the relation (number of rooms in the apartment, etc.).

Using calculated columns in Flow

The values ​​of the calculated columns can be read with Flow in the normal way.

ATTENTION! A change in the value of the calculated column doesn’t trigger the Flow, because it is not an update targeting the row (does not trigger the When row is created or modified trigger).

So Flow cannot be started when the cleaning percentage of the apartment becomes 100. Which would be really convenient. Of course, we will reach the same conclusion

• by starting flow every time the room is marked as cleaned. After this, we check whether all the rooms in the apartment have been cleaned
• by checking at regular intervals whether all the rooms in the apartment have been cleaned

Canvas Power App and calculated columns

Calculated columns can be used as filters and search criteria in Canvas Power Apps.

Items = Filter(Rooms, Apartment.'Cleaned %' > 35)

If the calculated column is behind the relation, its utilization is limited. Just like regular columns.

Which brings us to the next topic.

Concatenation of column values

Dataverse is not (from the Power Apps point of view) a freely queryable database. The most typical example of this is the following.

We want to show all rooms in all buildings in the gallery. Will succeed.

Items = Rooms

We want to show on every room which apartment it belongs to. No problem.

Text = ThisItem.Apartment.Name

In addition, we want to show which building the apartment belongs to.

Text = ThisItem.Apartment.Building.Name

This will not work. We can navigate from the room row only one relation forward (to the apartment). The building is behind two relations.

The problem can be solved by adding a calculated column (Name of the building) for the apartment.

The column contains the name of the building.

In this way, the building is also visible in the room listing. The necessary information is behind one relation in the apartment table.

Values ​​can be channeled through the entire relational chain in this way. If you want to search for rooms based on the name of the building, the name of the building must also be a calculated column in the room table.

Free word searches can only be done within the data source in Canvas Power Apps.

PowerFx column (preview)

Today, you can create columns whose value is defined directly with PowerFx.

No data type is defined for these columns at all, but it is determined automatically based on the PowerFx formula. After saving, you can no longer modify the PowerFx formula so that the data type of the final result changes to another.

So far, PowerFx columns correspond to calculated columns. Building summary columns with PowerFx is not yet supported.

Summary

Calculated columns are convenient. The calculation is done in one place (Dataverse), so the same logic does not have to be implemented separately for each application. If necessary, changes to the calculation are made centrally in one place.

When using summary columns, remember that they are not real-time. It unfortunately limits their use a lot.