Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 ,
try this in calculated column,
Status = IF('Table'[AllAirports] = 'Table'[Source] || 'Table'[AllAirports] = 'Table'[Destination],"Found","Not Found")
Thanks,
Arul
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.
@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
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.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |