i am currently trying to filter a report containing business trips and itineraries so it shows only those that have at least one business stop abroad.
In more general terms, i want to show all data for a specific value in column "Itinerary Key" if in one of two other columns "Departure Country" OR "Arrival Country" a certain condition ("<> Country") for the information connected to the value in column "Itinerary Key" is met.
So far i created a Query Calculation item ("[Itin Key if Trip abroad]") containing the expression: CASE WHEN ([Departure Country]<>[Country]) OR ([Arrival Country]<>[Country]) THEN [Itinerary Key] ELSE Null END
So i have a column that contains the Itinerary key, but only in the lines where the condition is actually met.
I then created a filter with the following expression: [Itinerary Key] in ([Itin Key if Trip abroad])
The idea here was to have a selection based on matching the itinerary key with the pool of itinerary keys that meet the condition on any line. However, it still only shows the lines where the Query calculation item actually generates a value. I want to show all the lines for the columns "Departure Country" and "Arrival Country" for each Itinerary Key where the condition from the query calculation is true at least once.
How can this be done?
I think you can accomplish by using this filter instead of the one you mentioned:
count([Itin Key if Trip abroad] for [Itenerary]) > 0
For every unique itenerary key we will count the non-null values (nulls are ignored in counts). If a specific itenerary has no rows that match the criterion, the count will return 0 and its rows will be excluded. If a specific itenerary has one or more rows that match the criterion, its count will be 1 or more and all rows for that itenerary will be included.