If related table contains "x" return "x" on a new column

[HendrixSpirit]

Dear all, I have the following issue:

Table 1 Related to Table 2 per Primary Key

Table 1

+-------------+--------+
| Primary KEY | FRUIT  |
+-------------+--------+
| 1           | ORANGE |
+-------------+--------+
| 2           | BANANA |
+-------------+--------+
| 3           | GRAPE  |
+-------------+--------+
| 4           | PLUM   |
+-------------+--------+

 Table 2

+-------------+-------------------+
| Primary KEY | Country of Origin |
+-------------+-------------------+
| 1           | EEUU              |
+-------------+-------------------+
| 1           | MEXICO            |
+-------------+-------------------+
| 2           | ECUADOR           |
+-------------+-------------------+
| 2           | BRAZIL            |
+-------------+-------------------+
| 2           | MEXICO            |
+-------------+-------------------+
| 3           | CHILE             |
+-------------+-------------------+
| 3           | ITALY             |
+-------------+-------------------+
| 4           | ARGENTINA         |
+-------------+-------------------+
| 4           | BOLIVIA           |
+-------------+-------------------+
| 4           | PERU              |
+-------------+-------------------+
| 4           | BRAZIL            |
+-------------+-------------------+

I need to create the formula on dax to have the following new column in table 1:

NEW TABLE 1

+-------------+-------------------+--------------------+
| Primary KEY | Country of Origin | NEW COLUMN RESULT  |
+-------------+-------------------+--------------------+
| 1           | ORANGE            | OTHER COUNTRIES    |
+-------------+-------------------+--------------------+
| 2           | BANANA            | OTHER COUNTRIES    |
+-------------+-------------------+--------------------+
| 3           | GRAPE             | CHILE              |
+-------------+-------------------+--------------------+
| 4           | PLUM              | CHILE              |
+-------------+-------------------+--------------------+

AS you can see, what I need is a dax formula, that search for the word "Chile" in the columns related totable 2, and return "CHILE" in table 1 if chile is found on the related table, and return "other countries" if chile is not found. 

I've tried a lot of different ways,  but I cannot manage to do it.
As all options I've tried, always POWER BI says to me different errors like, 

"The MIN function only accepts a column reference as an argument."

I get i have to combine in some way the "IF" formula" with the "Related" formula, but I can't manage to get it.
Been googling for hours. 

Thanks in advance.

[Yggdrasill]

Try this:

In advanced editor you can paste this

#"Merged Queries1" = Table.NestedJoin(#"Changed Type",{"ID"},countries,{"ID"},"countries",JoinKind.LeftOuter),
    #"Expanded countries" = Table.ExpandTableColumn(#"Merged Queries1", "countries", {"Country"}, {"Country"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded countries", "Country2", each if [Country] = "Chile" then "Chile" else "Other countries", type text),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"ID", Order.Ascending}, {"Country2", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Fruit"})
in
    #"Removed Duplicates"

Where

• "Fruit" is the fruit table
• "countries" is the table with the Country of Origin

I get this

[HendrixSpirit]

Dear Yggdrasil,

Thank you for your answer,

1) I will test it today, but I was looking to have it solved adding a new column in the "Data View" not in power query advanced editor, as sometimes i would have to change the "country" "on the fly", with no access to the database.

2) Plum should say "Chile" not "Other Countries", so it's not working ok.   <--- Edit. I was wrong, it's working ok.

[jthomson]

---

2) Plum should say "Chile" not "Other Countries", so it's not working ok.

---

Your example table doesn't list Chile as part of option 4, so I don't get why it should?

[HendrixSpirit]

Jthomson
2) Plum should say "Chile" not "Other Countries", so it's not working ok.

Your example table doesn't list Chile as part of option 4, so I don't get why it should?

You are right!!!!

I thought I had written Chile on table 1 point 4 (i did it at 04:00 am the question). it shouldn't say chile, the answer is ok.

Any idea of a possible solution on "Data View" module of power bi desktop?

I will try the option of advanced editor now.

[HendrixSpirit]

Jthomson
2) Plum should say "Chile" not "Other Countries", so it's not working ok.

Your example table doesn't list Chile as part of option 4, so I don't get why it should?

[Zubair_Muhammad · Accepted solution]

@HendrixSpirit

As a calculated column using DAX... you can try

Column =
IF (
    CONTAINS ( RELATEDTABLE ( Table2 ), Table2[Country of Origin], "CHILE" ),
    "CHILE",
    "OTHER COUNTRIES"
)

[Vidya1882]

Hi..

       i have two table A and B

i want a new table using dax like below.how do i solve it

[ssingh92]

Hi, Hope you're well! If 0 comes in a column then need to replace those with new column values but that column is in another table, which it is not picking up. I tried if ([table1]column1 = 0, [table2],column2) Thanks, Shashank

[HendrixSpirit]

@Zubair_Muhammad

Dear Zubair, That was exactly what I was looking for, now i can even do nested if, if i want to find 2 countries.

Thank you very much!!!