Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'd like to recreate my data model in a more efficient way.
I have a Table1 with transaction data, this data is also geocoded an contains only unique rows. However, I also have Table2 which contains the nearby locations related to Table1.
Table1
TransactionYear | Location | Lat | Long | Value_1 | Value_2 |
2019 | Location_1 | 19.03336 | 29.6116 | 21 | 193 |
2019 | Location_2 | -14.258 | 42.95751 | 192 | 99 |
2019 | Location_3 | -66.9449 | -38.4787 | 118 | 15 |
2019 | Location_4 | -59.3971 | -60.5873 | 110 | 53 |
2019 | Location_5 | -60.0055 | -19.878 | 40 | 82 |
2019 | Location_6 | -35.9316 | -118.366 | 108 | 155 |
2019 | Location_7 | 66.3981 | -52.6308 | 44 | 1 |
2019 | Location_8 | 39.34878 | -112.259 | 128 | 170 |
2019 | Location_9 | 57.37424 | -151.717 | 50 | 197 |
2019 | Location_10 | 17.8441 | -60.5622 | 144 | 161 |
Table2
Location | Related_Location |
Location_1 | Location_2 |
Location_1 | Location_3 |
Location_1 | Location_4 |
Location_10 | Location_1 |
Location_10 | Location_3 |
Location_10 | Location_6 |
Location_2 | Location_1 |
Location_2 | Location_10 |
Location_2 | Location_5 |
Location_3 | Location_4 |
Location_3 | Location_7 |
Location_3 | Location_8 |
Every time I select one record from the table on the left I'd like to display the selected location (middle map) and all the nearby locations (right hand map).
Displying the selected location on the middle map is easy, however I need the location of the nearby locations too. Currently I'm using lookupvalue (I could use merge in Power Query too) to get the lat-long value pairs from the first table, but I think that this is creating a lot of redundancy as I already have this info in Table1. All in all I believe that this may not be the best way, but I couldn't come up with a better way to create this when playing around with different "fact" tables or relationships.
Any idea how to improve the model?
Example file here.
Thanks,
PDG
Solved! Go to Solution.
@P_D_G , a simple but tricky solution to your issue is to append a "self-referenced" table to Closest_Locations table in PQ,
In the meantime, retrieve lat/long info from Main_Table by simply merging them in PQ; then when you choose one location from Main_Table, the chosen location, along with all related locations show on the map simultaneously.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@P_D_G , a simple but tricky solution to your issue is to append a "self-referenced" table to Closest_Locations table in PQ,
In the meantime, retrieve lat/long info from Main_Table by simply merging them in PQ; then when you choose one location from Main_Table, the chosen location, along with all related locations show on the map simultaneously.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I knew I was overcomplicated things... Thanks for the help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |