Forum Discussion
Resolver IRelationship between lookup tables
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 :robothappy:
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!
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
2 Replies
v-lili6-msftCommunity Support
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
ValentinBIAThank 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