cancel
Showing results for 
Search instead for 
Did you mean: 
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



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

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


Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Helpful resources

Announcements
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.

Top Solution Authors