Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power BI DAX Calculated Column - not in other 2 columns

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. 

 

AJAJ_0-1678975701647.png

 

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

 

 

1 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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


View solution in original post

4 REPLIES 4
Arul
Super User
Super User

@Anonymous ,

try this in calculated column,

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

Thanks,

Arul





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Anonymous
Not applicable

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. 

 

AJAJ_0-1678978479505.png

 

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Anonymous
Not applicable

@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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.