Can't get USERELATIONSHIP to work

[P_Allert]

Hi folks,

I have a fact table for Flights (Called 'Flights') and a dimension table containing Airports (Called 'Airports').

• Each row in 'Flights' represents a single flight, performed on a certain date. It has columns for an Origin Airport (POD) and a Destination Airport (POA). The columns contain numeric AirportIDs.
• Flights mostly have different POD and POA, however it is also common that a flight has the same airport as POD and POA.
• The AirportID is also present as a column in the 'Airports' table. It contains unique numeric values.
• The Airport IDs in the POD-Column and the POA-Column in 'Flights' are n:1 related to the AirportID column in the 'Airport' Table.
• As the 'Flight' table has two different columns containing an AirportID, the relationships need to be inactive (Only one can be active, but I chose two have them both inactive).
• Combinations of Airports (POD-POA) are not unique and therefore can appear multiple times in the 'Flights' table.
• The 'Airport' table also contains a column called IATA. The IATA Code of an Airport is a three-letter text identifier, which is unique.

I'd like to create DAX measures (Not a calculated table column), which I can use in a table visual, which allow me to display data from the 'Flights' Table, but showing the IATA Codes of POD and POA instead of the AirportID values.

So basically I'd prefer this:

Over this:

I managed to do this with as calculated columns like below, but would prefer explicit DAX measures, which I can use with more flexibility.

POD IATA =
LOOKUPVALUE(
Airports[IATA],
Airports[AirportID], Flights[POD]
)

This looked like a promising solution, but produces an error "A table of multiple values was supplied where a single value was expected":

POD IATA =
CALCULATE(
VALUES(Airports[IATA]),
USERELATIONSHIP(Flights[POD], Airports[AirportID])
)

I have been searching the web for hours now for clues on what I did wrong here, but am probably asking Google the wrong questions.

Can anyone help me out with this?

Thanks.

Best regards
Patrick

[Fowmy · Accepted solution]

@P_Allert 

Please try: 

POD IATA Measure = 
VAR SelectedPOD = SELECTEDVALUE(Flights[POD])
RETURN 
    MAXX(
        FILTER(
            Airports,
            Airports[AirportID] = SelectedPOD
        ),
        Airports[IATA]
    )

[P_Allert]

Hi Fowmy,

thanks. It works beautifully. 👍

Best regards

Patrick

[v-xinruzhu-msft]

Hi @P_Allert 

Did the solutions Fowmy  and Greg_Deckler offered help you solve the problem, if them help, you can accept them as solutions so that more user can refer to. or if you have other problems, you can provide some informaiton so that can offer solutions for you.

Best Regards!

Yolo Zhu

[Greg_Deckler]

@P_Allert You need to aggregate your VALUES somehow. Perhaps try to wrap it in a CONCATENATEX.