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 - LOOK up for a value in another table.

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:00Emirates01/09/21 16:00:00
1/9/21 11:59:00Qatar Airways01/09/21 17:00:00
1/9/21 11:59:00Turkish Airlines01/09/21 06:00:00
2/9/21 11:59:00Singapore Airlines02/09/21 16:00:00
2/9/21 11:59:00Emirates01/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

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1631251072927.png

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.

View solution in original post

2 REPLIES 2
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1631251072927.png

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
Super User
Super User

@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())

 

 

 

 

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.