Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

I need to create a calculated column based on some rules which are in a another table

The rules are as below (example) -

1. The original table has columns Country, Product and Revenue. 

2. A new column is to be created based on the rules mentioned below. These list of rules is pretty huge (Approx 100+) Hence a manual switch statement or a if else  loop would not be a suitable approach. 

3. I can map the rules for which there is only 1unique value such as Row 1, however how to deal with condition such as row 2, where it is applicable for all the countries.


I was thinking of using a lookupvalue function to map the outcome, but for instances such as row 2, it becomes difficult. 


Row NUmberTarget ColumnCountryProductRevenue
4X1US<ALL>>50 and <100
Super User
Super User

@lalTel20 , You can use lookup, You can call the other table in filter


refer these


refer 4 ways to copy data from one table to another

Hi Amit, 
Thanks for your response. Yes I am currently doing it the same way.

However for the second row where the country maps to any country, in this scenario how can we look up. 

SourceLookup Table
will not be matched for countries<Any>


The thir instance in above scenario will not match for any countries from source. One solution I can think of is to replicate the combination for all the listed countries.  What could be another easier one. 

@lalTel20 , I such scenario, Create a table like this and join country with the country all to get all rows


Merge in power query

Country Country All
Germany Germany
US All
Germany All

Helpful resources

November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors