Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
@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())
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |