PowerBI multiple foreign key columns

Question

I have the a table structure similar to this simplified example in my Data Warehouse:

Table "Records":

• ID
• Date
• Car
• Truck

Table "Vehicles":

• ID
• VehicleType
• LicensePlate

Now I want to visualize the data with PowerBI. The problem occurs when I try to add relations. Because from the "Records" table both the "Car" and "Truck" columns are foreign keys referencing the "Vehicles" table, PowerBI throws the following error:

You cannot create a direct active relationship between x and y because there is already an active set of indirect relationships between these tables.

Answer 1

You can create two separate tables in your model, VehiclesCars and VehiclesTrucks. Filter them on VehicleType to contain only the relevant rows.

Or if you want to have a list with all vehicles, create a computed column in Records table like this:

VehicleId = IF(ISBLANK(Records[Car]); Records[Truck]; Records[Car])

And create only one foreign key between Records and Vehicles: