Calculated value based on text values in two other columns - matrix lookup

(0) Share

Report

Posted on by evgiles

First time posting. Just getting into Dataverse.

I am looking for a way to set the value of a column based on the values from two other columns.

You can think of the values as being pulled from a matrix like this:

For example, if the "Operational Risk Level" column is set to Medium and the "Data Classification" column is set to Sensitive, I want to set a value of High in the "Inherent Risk" column.

I am looking to avoid using anything outside of Dataverse to set this value (i.e., I do not want to set the value using Power Automate).

Currently, I am the value using a very long series of If-then-else statements which is not very scalable (in case I ever want to do something similar) and I am concerned about how well this might work in the end. I have to believe there is a better way of doing this.

Any ideas?

Thanks.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (1)

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Hi @evgiles,

You can use Formula column in Dataverse for the "Inherent Risk" columnn with a switch statement that will be a little better than ifs.

More info: https://learn.microsoft.com/power-apps/maker/data-platform/formula-columns

Hope this helps!

Was this reply helpful? Yes No
evgiles 11 on at

Like (0)

Report

Eric,
Thanks for this suggestion. I am trying that for another column, and it seems to be working okay, but it sure takes a long time to construct it and it's not very scalable.

It would be great to have something akin to a two dimensional array lookup. That way this type of configuration might be easily maintained in a table.

Appreciate the quick response,
Ed

Was this reply helpful? Yes No
Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report

Hi @evgiles,

Unfortunately within the same table it's not possible, formula columns would be the way to go. You can create a matrix tbale to configure the Inherent Risk, like a "Inherent Risk Configuration" table that has 3 global choice columns; "Operational Risk Level" , "Data Classification" and "Inherent Risk". Then as you already mentioned with a Power Automate flow, lookup the correct Inherent Risk form that table...

Feel free to submit that idea however at: https://ideas.powerapps.com/d365community/forum/f1458c72-ae29-ed11-9db2-000d3a8c451e

Hope this helps!

Was this reply helpful? Yes No