Forum Discussion
Resolver IDate / Lookup Tables
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 !
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!
2 Replies
JoHo_BIResponsive Resident
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!
ValentinBIAHi JoHo,
Thank you very much, that is exactly what I was looking for !
(glad you noticed the Skyrim reference :) )