IF condition + WHERE

[gianmarco]

Dear All,

i have the following table:

Fruit	City	Preferred
Banana	NY	0
Kiwi	null	null
Apple	Rome	1
Orange	null	null
Grape	Philly	0

I need to create a conditional column for which: IF City = null THEN City where Preferred = 1 ELSE City

Desired result as follows:

Fruit	City	Preferred	NEW COLUMN
Banana	NY	0	NY
Kiwi	null	null	Rome
Apple	Rome	1	Rome
Orange	null	null	Rome
Grape	Philly	0	Philly

Thank you a lot
gianmarco

[ChiragGarg2512 · Accepted solution]

@gianmarco , If this is the data then this column formula should work:

Column =

var _v = CALCULATE(Max(q1[City]), FILTER(q1, q1[Preferred] = 1))

return

IF(q1[City] = "null", _v, q1[City])

Thank You.

[ray_aramburo]

Just a question to give you the best logic and solution. When Preferred = 1 the expected outcome should always be "Rome" or are you expecting other cities as well?