Create AND/OR groups for your calculated fields in Model Driven Apps/Dynamics 365? DataVerse Power Fx columns can help you achieve it.

By / August 25, 2022

Hello everyone and welcome to my blog. Today I am going to discuss about a recently release topic. In fact it’s in preview stage but I am pretty sure this cool stuff is certainly going to be in General Availability soon.

Calculated columns is there with Dynamics 365 and now DataVerse for quite some time now. They are useful but they lack one of the most basic features – Grouping while defining the conditions.

Lets take a very basic example. I have a column named “User rating“. It’s a choice field. Below is the configuration for the field.

Condition groups in calculated fields of Dynamics 365/ Model Driven apps

I have another column – Verified user which of Yes/No (boolean) field.

My requirement is to create a calculated field – Rating outcome with the below formula.

If ('user rating' = 1 Or 'user rating' = 2 ) and verified user = Yes Then "Excellent"
If ('user rating' = 2 Or 'user rating' = 3) and verified user = Yes Then "Satisfactory"
Else "needs improvement"

I configure this calculated field and in the formula editor, I try to set up the above logic. However below is the limitation

Condition groups in calculated fields of Dynamics 365/ Model Driven apps

I can either have all AND clauses or all OR clauses. I cannot have any condition groups.

Well no longer. In preview, we now have the support for Power Fx formula columns where you can get rid of these limitations.

Since the feature is in preview, navigate to https://make.preview.powerapps.com

Now when you create a column, you have an option ‘Formula’.

In the formula column, you can define formula to achieve the desired result. The supported list of functions currently available are mentioned here.

Condition groups in calculated fields of Dynamics 365/ Model Driven apps

Below is the formula

If(
    ('User Rating' = 'User Rating (Accounts)'.'1' Or 'User Rating' = 'User Rating (Accounts)'.'2') And 'Verified User', 
    "Excellent",
    ('User Rating' = 'User Rating (Accounts)'.'3' Or 'User Rating' = 'User Rating (Accounts)'.'4') And 'Verified User', 
    "Satisfactory", 
    "Needs Improvement")

Wonderful isn’t it? Well, let’s try it out. Below is the behavior in action.

This is a preview version and there are certain limitations. Please check this documentation to know more about this feature.

Hope this helped. You will also like the below posts.

Debajit Dutta
Business Solutions MVP