Power BI DAX Calculated Column - not in other 2 columns

[Anonymous]

Hi there,

I'm looking for help to calculate a "status" column where Allairport value is not in source or destination. Its a flat table. For similicity just represented the sample PBI data as below.

You could see G is not found anywhere in source or destination. 

In SQL world, query would be something like below

UPDATE TABLE
SET AllAirports = 'Found'
where AllAirports in
(
select distinct source as a from table
union
select distinct destination from table
)

UPDATE TABLE
SET AllAirports = 'Not Found'
where AllAirports not in
(
select distinct source as a from table
union
select distinct destination from table
)

Thanks

[Arul]

@Anonymous ,

try this in calculated column,

Status = IF('Table'[AllAirports] = 'Table'[Source] || 'Table'[AllAirports] = 'Table'[Destination],"Found","Not Found")

Thanks,

Arul

[Anonymous]

Thanks. I'm afraid it didnt work by just comparing 1 row alone. The value should be searched on all the rows of source and destination and then compute if it is found or not found. In this case E is found. 

[Arul · Accepted solution]

@Anonymous ,

Try this,

Status = 
VAR _col1 = DISTINCT('Table'[Source])
VAR _col2 = DISTINCT('Table'[Destination])
VAR _union = UNION(_col1,_col2)
VAR _result = IF('Table'[AllAirports] IN _union,"Found","Not Found")
RETURN _result

Thanks,

Arul

[Anonymous]

@Arul 

is there a way to use where / filter condition along with distinct. Something like this

VAR _col1 = DISTINCT('Table'[Source]), Filter('Table',len(Table[SomeotherColum])> 0) -- this is not working. I'm trying to add distinct source with where condition.