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

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.

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

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.

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