Understanding Calculated Columns in Dataverse can be a delight for those without a Dynamics 365 background. This feature holds two types of calculated columns: Rollup and Calculated Columns. A Rollup is a collection of rows related to a particular row; for example, how many contacts are related to a specific customer. On the other hand, a Calculated Column involves calculations performed between column values such as the average from columns A, B, and C. You can also use the columns found behind the relation in the calculation.
Let's illustrate these concepts: suppose we have a solution that maintains information about the cleaning of rooms, apartments, and residential buildings. In this data model, each room connects to an apartment, and each apartment connects to a building. The data is maintained with model-driven Power Apps. Individual rooms are marked as cleaned using mobile Power Apps. Now, if we want to show the number of cleaned rooms per apartment in the tools, easily done with a rollup column.
Similarly, for more precise data such as the cleaning percentage of rooms in an apartment, we create another summary column, and the percentage is calculated using the values of the previous columns. However, 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 and updated once an hour. The values can also be updated manually with the Recalculate button.
The post also talks about updating summary columns with Flow and how calculated columns can be used in Canvas Power Apps. It also covers the use of Power FX columns in preview mode. Remember, while using summary columns, they are not real-time which limits their uses.
The calculated columns in Dataverse offer convenience by carrying out calculations in one place, eliminating the need to implement the same logic separately for each application. This feature enhances efficiency, as changes to the computation can be made centrally. This simplifies the data management process significantly. However, the fact that summary columns are not real-time can restrict their overall usage.
The main topic of the text is understanding calculated columns in Dataverse. Those without a Dynamics 365 background might find Dataverse challenging because it always has new features to explore. This particular discussion is focusing on calculated columns, of which there are two types: rollup and calculated column. The rollup column represents a collection of rows related to a row, such as how many contacts are related to a particular customer. A Calculated column refers to calculations performed between column values like the average of columns A, B, and C. The text provides an example about maintaining information about the cleaning of rooms, apartments, and residential buildings. There is also a discussion on creating a new column for the apartment using rollup, with the aim of showing the number of cleaned rooms per apartment.
Microsoft certified professional, expert in Microsoft solutions, specialist in Microsoft technologies, Microsoft trained expert, Microsoft technology consultant