CODEWITHSUNDEEP
powerbi
nilsinelabore
nilsinelabore

Reputation: 5095

Create column using multiple conditions and tables in Power BI

I would like to create a column for a list of products based on some other tables in Power BI. table1 is a full list of products I want to investigate,

table1:

id  product_name 
1   xl1
2   xl2
3   s11
4   m11
...

I want to create a column city based on another table, table2:

id  product_name   city
1   xl1            D
2   xl1            A 
3   xl1            A 
4   xl1            A 
5   xl1            A 
6   s11            B
7   s11            B
8   s11            B
9   m11            C

If product_name in table1 is also found in table2, look for city, if there is only one value, fill the new column with the value, if there are two values, write 'both', if product_name cannot be found in table2, write 'no value'

Expected output:

id  product_name  new_col
1   xl1           both   (because city =D,A in table2)
2   xl2           no value
3   s11           B
4   m11           C
...

How should I approach a problem like this in Power BI? Any advice appreciated.

Upvotes: 0

Views: 1823

Answers (1)

Kin Siang
Kin Siang

Reputation: 2699

You may use the following dax measure to achieve your outcome, so when you know that the product appear in more than 1 unique city, it will return both and accept the solution if help :)

Column = 
var result1 = CALCULATE(FIRSTNONBLANK(Table2[city],1),
                FILTER(ALL(Table2),Table2[product_name] = EARLIER(Table1[product_name])))
var distint = CALCULATE(DISTINCTCOUNT(Table2[city]),
                FILTER(ALL(Table2),Table2[product_name]=EARLIER(Table1[product_name])))
return
IF(distint>1,"both",IF(ISBLANK(result1),"no value",result1))

enter image description here

Upvotes: 1

Related Questions