Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lalTel20
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. 

 

  Inputs
Row NUmberTarget ColumnCountryProductRevenue
1A1USA<50
2C1<ALL>C>100
3D1GermanyD<Any>
4X1US<ALL>>50 and <100
3 REPLIES 3
amitchandak
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
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

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
USUS
GermanyGermany
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
US US
Germany Germany
US All
Germany All

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors