Power BI - LOOK up for a value in another table.

[Anonymous]

I have a table which contains a list of all flights

Emirates

American Airlines

Singapore Airlines

Qatar Airways

Turkish Airlines

I have another table which gives me the landing date/time for each of the above flight. The flights below are daily flights and we are expecting them to be landing daily.

1/9/21 11:59:00	Emirates	01/09/21 16:00:00
1/9/21 11:59:00	Qatar Airways	01/09/21 17:00:00
1/9/21 11:59:00	Turkish Airlines	01/09/21 06:00:00
2/9/21 11:59:00	Singapore Airlines	02/09/21 16:00:00
2/9/21 11:59:00	Emirates	01/09/21 16:00:00

I have a dashboard page for all flights arrived and expected flights. As you can see on 01/09 Singapore Airlines and American Airlines are missing. How do I get a list of all the flights which are missing ona particular day?

ara

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Create a calculation table summarizing the dates of the flights and create a slicer with the summarized dates, then create a measure to calculate the missing flights.

DateList = SUMMARIZE('Table',[takeoff time].[Date])

_isOn = 
VAR _a =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), [takeoff time].[Date] = MAX ( 'DateList'[Date] ) ),
        [flights]
    )
RETURN
    IF ( MAX ( 'Flights'[flights] ) IN _a, 1, 0)

 So we can create a table with the flight field and show items when value is 0 in filter pane.

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , If both tables are joined and you have a slicer of dates, use this measure with airlines from first Table

measure =

var _cnt  = countrows(Table2)

return

if(isblank(_cnt), 1, blank())