Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors