Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 NUmber | Target Column | Country | Product | Revenue |
1 | A1 | US | A | <50 |
2 | C1 | <ALL> | C | >100 |
3 | D1 | Germany | D | <Any> |
4 | X1 | US | <ALL> | >50 and <100 |
@Anonymous , 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.
Source | Lookup Table |
US | US |
Germany | Germany |
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.
@Anonymous , 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 |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
15 | |
13 | |
11 | |
9 | |
8 |