The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
92 | |
90 | |
72 | |
69 |
User | Count |
---|---|
232 | |
128 | |
117 | |
82 | |
82 |