Relationship between lookup tables

[ValentinBIA]

Good morning dear Power BI Community!

I'm stuck with an issue, and I lack the DAX knowledge to solve it. I hope you guys can help

I have:

1 DATA Table: 'Rentals'

3 LOOKUP Tables: 'Customers', 'Station' and 'Geography'

I have also used calculated tables to create new LOOKUP tables so that my relationships don't loop (cf. Picture below):

Now, out of this file, I need to build a report that shows two things:

- The percentage of rentals made from one town to the same town (for example Whiterun to Whiterun)

- The percentage of rentals made from customers living in a town, that either leave from or arrive to the same town (as the one the customer lives in)

The data in my different tables is the following:

Geography:

Postcode	Town
158	Whiterun
268	Helgen
348	Whiterun
486	Whiterun
524	Helgen
639	Morthal
792	Whiterun
807	Morthal
934	Morthal

Station:

Station ID	Postcode
A	158
B	486
C	268
D	524
E	807
F	639

Customers:

Customer ID	Postcode
1	158
2	268
3	348
4	486
5	524
6	639
7	792
8	807
9	934

Rentals:

Rental ID	Customer ID	StartStation	EndStation
1	6	A	C
2	1	D	A
3	4	E	E
4	8	B	D
5	5	B	C
6	9	D	F
7	7	B	E
8	9	C	D
9	6	B	A
10	4	C	A
11	5	E	B
12	6	A	A
13	7	C	B
14	2	D	C
15	3	A	D
16	8	C	A
17	2	F	F
18	8	F	C
19	1	A	C
20	9	C	F
21	1	A	E
22	1	C	E
23	1	E	A

Sorry for the (very) long post, and huge thanks to anyone that tries to solve my issue!

PS 1: My original data is obviously not this, but for privacy reasons, I'm not allowed to upload it. However, the structure is the same (with more lines and columns that don't matter for what I'm trying to achieve here).

PS 2: I've made a Power BI File with this data in it: https://we.tl/t-GoBQNqfe9J

PS 3: I know it's a lot to ask, but if you could not just give me the fish, but also teach me how to fish, that would be awesome!

[v-lili6-msft · Accepted solution]

Hi@ ValentinBIA 

After my test , you may need to optimize your model, you can try to do these like below:

Step 1:

you can summary the table Geography and table Customers into one table,  table GeographyStart and table StationStart  into  one table and table GeographyEnd and table StationEnd  into  one table

like these:

Basic Geography = SUMMARIZE(Geography,Geography[Postcode],Geography[Town],Customers[Customer ID])

StarGeography = FILTER(SUMMARIZE(GeographyStart,StationStart[Postcode],StationStart[Station ID],GeographyStart[Town]),ISBLANK(StationStart[Postcode])=FALSE())

EndGeography = FILTER(SUMMARIZE(GeographyEnd,StationEnd[Postcode],StationEnd[Station ID],GeographyEnd[Town]),ISBLANK(StationEnd[Postcode])=FALSE())

Step 2:

Create relationship between them with Rentals like this

Try these two measure:

The percentage of rentals made from one town to the same town (for example Whiterun to Whiterun)

The percentage of rentals first = DIVIDE(CALCULATE(COUNTROWS(FILTER(Rentals,Rentals[StartStation]=Rentals[EndStation]))),CALCULATE(COUNTROWS(Rentals),ALL(Rentals)))

The percentage of rentals made from customers living in a town, that either leave from or arrive to the same town (as the one the customer lives in)

The percentage of rentals second = CALCULATE(COUNTROWS(FILTER(Rentals,RELATED('Basic Geography'[Town])=RELATED('StarGeography'[Town])||RELATED('Basic Geography'[Town])=RELATED('EndGeography'[Town]))))/CALCULATE(COUNTROWS(Rentals),ALL(Rentals))

Result:

Here is demo, please try it

https://www.dropbox.com/s/185bdrt8cs9jwfp/new_rentals_Customers.pbix?dl=0

Best Regards,

Lin

[ValentinBIA]

Thank you very much @v-lili6-msft, this is exactly what I was looking for.

The only tweak I would add (now that I understood how you did it) is the following:

For the first measure, your answer gives the rentals from station A to station A, and not from town Winterfell to town Winterfell, here is how to fix it

Town to same town = DIVIDE(CALCULATE(COUNTROWS(FILTER(Rentals,RELATED(StarGeography[Town])=RELATED(EndGeography[Town])))),[TotalRentals])

I also added a measure of Total Rentals, to be able to slice the values with months.

Best,

Valentin