Date / Lookup Tables

[ValentinBIA]

Morning dear Power BI users!

I am working on the data for car rentals. The cars leave from a station and parks in a station.

In my main query, I have the StationFrom column, the StationTo, and the number of rentals.

StationFrom	StationTo	Rentals
A	A	4
A	B	5
A	C	1
B	A	4
B	B	5
B	C	9
C	A	3
C	B	2
C	C	7

I also have a lookup table that gives me more information on the location of the station.

Station	Lat	Long	City
A	550	5	Hogwart
B	50	555	The Shire
C	1400	469	Whiterun

However, it does not seem like I can link both the StationFrom and StationTo columns to my Location table (to Station column).

Do you guys know if I can link one column of a Lookup Table to 2 different colums of my main query ? Or should I just copy paste my Lookup table and use two of them?

Thank you very much !

[JoHo_BI · Accepted solution]

Hi Valentin,

Always happy to help a Skyrim fan!

You can create a relationship to both, but only one will be active. But then in your calculations you can use the USERELATIONSHIP function to reference the inactive relationship instead. For example:

No. Rentals = CALCULATE(SUM(Table[Rentals]), USERELATIONSHIP(LookupTable[Station], Table[StationFrom]))

Otherwise, there is a LOOKUPVALUE function that may be of use.

Hope that helps! 

[ValentinBIA]

Hi JoHo,

Thank you very much, that is exactly what I was looking for !

(glad you noticed the Skyrim reference 🙂 )