Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danielc7150
Frequent Visitor

Mapping locations with drill-down into sublocations

Hello,

I am currently mapping a few locations of student accomodation - there are about 7 'main' areas.

These areas then drill-down into smaller 'blocks' of accomodation.

I have the coordinates on a different table to my big table of data. The problem is that these blocks are numbered, and because they are numbered they are not distinct from other 'main areas' and so i cannot form a relationship with them to the main table due to them having to be 'one-to-many'.

 

I realise that I could give them a prefix but this would involve massive changes to the original table as the whole table is made using references and frankly that would take a very long time to change.

 

So I was wondering if there was any way to pair the 'main area's with their smaller 'blocks' so that I would not run into this problem.

 

 

 

Thanks,

Dan

1 ACCEPTED SOLUTION

Hi  @danielc7150 - I can get a test to work the way I think you want it to by doing this:

 

Location

Coordinates [Site]

Coordinates [Block]

 

Latitude 

Average of Coordinates [Latitude]

 

Longitude

Average of Coordinates [Longitude]

 

Size

Count of 'Processed Jobs' [Area/Block] <-- note this is from 'Processed Jobs' NOT 'Coordinates. Also, make sure this is set to Count and not Count (Distinct)

 

I only have one relationship, and that is from Processed Jobs (Area/Block) to Coordinates (Block)

 

If I set it up like this, the bubbles at the size level are sized based on the total number of 'jobs' for the entire site. When I drill in, the bubbles are sized by the total number of 'jobs' for each block. 

 

 

 

View solution in original post

13 REPLIES 13
MalS
Resolver III
Resolver III

Do you have a column for 'Main Area' and 'Blocks' in each table? If so, it's pretty easy to merge both columns together in each table using the Query Editor. Then you can create a relationship between the merged columns. 

 

Just open the Query Editor, select the first table, select the 'Main Area' column, hold down CTRL and select the 'Blocks' column, then on the ribbon click Add Column > Merge Columns. Repeat for the second table. 

Thanks for the reply.

This worked, and was able to make the data drill down.

However, the circle size is relative to a 'count' of entries for each area. When I drill down, this 'count' stays the same for each individual block - it stays the same as the count for the whole 'area'. Any advice on this?

Glad the merge worked. 

 

Not sure about the count issue without seeing the data and the relationships, but you probably want your measure to count the entries per block. That way, when you have drilled down it will only show the count per block, and when you drill up it should automatically combine the counts for all the blocks in each area (because there is a relationship between areas and blocks). 

 

If you are having trouble, perhaps you could post some screenshots of your data and the relationship you have set up.

edit: this is actually the right hand side of the table.The left hand side of the main table - just to show the nature of it. Can be multiple entries for each block.The left hand side of the main table - just to show the nature of it. Can be multiple entries for each block.The 'block' column here is linked to the area/block column in the main table.The 'block' column here is linked to the area/block column in the main table.Here is the part that shows area/block - this is the column that links to the 'block' column in the coordinates sheet.Here is the part that shows area/block - this is the column that links to the 'block' column in the coordinates sheet.This is the visualisation and where each entry comes from - I have tried various combinations. The most succesful one managed to show a separate number for the site and the block, but then drilling down into the blocks placed them all in the same (average) location.This is the visualisation and where each entry comes from - I have tried various combinations. The most succesful one managed to show a separate number for the site and the block, but then drilling down into the blocks placed them all in the same (average) location.

Sorry if this is a bit confusing to understand, not sure how else to display it!

 

Thanks,

Dan

Oh - ok. That helps me understand. 

 

In Location, you appear to have Area/Block from the Processed Jobs table. Try replacing that with the Block field from the Coordinates table.

When I swapped this, it allowed me to drill-down into the individual blocks, but the blocks all kept the same 'count' as the overall site. 

 

View of all blocks (size relative to count of entries of each block contained within the site)View of all blocks (size relative to count of entries of each block contained within the site)Drilling down into the site - all blocks retain the same count as the overall site.Drilling down into the site - all blocks retain the same count as the overall site.

Any ideas? The count is still of blocks, from the main table of entries.

Sean
Community Champion
Community Champion

Well the count at the block level for each block is 1.

The Maple Bank Site has a count of 11 - the size of the Site Buble is 11 (at least from I see on the picture you posted)

when you drill down you'll see the 11 Blocks and the count is 1 at each so the size is 1 for all

How do you want to determine their size?

Thanks for the reply @Sean.

 

The size needs to be determined by the amount of total job entries in the table of total jobs (processed jobs) for that specific area.

 

For example, if Maple bank block 1 had 5 reported jobs, and Maple bank block 2 had 7 reported jobs. The overall area of maple bank (before drilling down) should show a bubble of size 12, and the two Maple Bank blocks should be a bubble relative to the count of jobs there.

 

At the minute, each block in maple bank is just taking on the total for the whole of maple bank, instead of having separate ones.

 

power.PNGThe drill down works with this set up (block taken from coordinates table), however each individual block takes on the same count as the site that it belongs to.The drill down works with this set up (block taken from coordinates table), however each individual block takes on the same count as the site that it belongs to.power3.PNGThe location drill down doesn't work with this set-up (block area from the processed jobs table), but each bubble shows the correct size.The location drill down doesn't work with this set-up (block area from the processed jobs table), but each bubble shows the correct size.

So as you can see from both of the drill-downs, currently I can only have one or the other, from correct bubble sizes to correct map placement.

 

Sean
Community Champion
Community Champion

Okay create a Measure which you'll use for the Size - it should look something like this...

Size Measure = IF ( HASONEVALUE( Sites[Block] ), AVERAGE(Main[Max Days]), COUNTA(Sites[Site]) )

the True part of the IF should calculate the Size when at the Block Level and the False when at the Site Level

Does this make sense?

Map Drilldown - Size Measure.gif

Let me know if you have any questions! Smiley Happy

So, the site and block for location should both be from the table listing them with the coordinates, right?

 

Unfortunately I wasn't able to get it to work 😞 your visualisation looks perfect though!

 

The table below shows the main 'jobs processed' table and how they are entered.

What's weird is that by using bar charts, i am able to drill-down successfully and it will correctly show the amount of jobs by the overall site, then the block, etc.

 

However it just cannot map them correctly with location.

 

blue1.PNG

So, for the formula:

I have values for site and block in both the table containing all accomodations with coordinates (the '1' in the 1-to-many relationship), as well as the table with many entries (the 'many', pictured above).

Which versions of site and block do i include in each section of the formula? I've tried a few different combinations and cannot get it to work.

 

Thanks so much for your help so far, and sorry if I'm not understanding something quite simple!

 

Dan

Sean
Community Champion
Community Champion

Can you post the formula of the Measure you are trying to use?

Hi  @danielc7150 - I can get a test to work the way I think you want it to by doing this:

 

Location

Coordinates [Site]

Coordinates [Block]

 

Latitude 

Average of Coordinates [Latitude]

 

Longitude

Average of Coordinates [Longitude]

 

Size

Count of 'Processed Jobs' [Area/Block] <-- note this is from 'Processed Jobs' NOT 'Coordinates. Also, make sure this is set to Count and not Count (Distinct)

 

I only have one relationship, and that is from Processed Jobs (Area/Block) to Coordinates (Block)

 

If I set it up like this, the bubbles at the size level are sized based on the total number of 'jobs' for the entire site. When I drill in, the bubbles are sized by the total number of 'jobs' for each block. 

 

 

 

Many thanks to everyone that contributed!

It seems as though I was missing a relationship between the two 'blocks' entries - one which I thought I had implemented, but apparently did not!

 

Thanks again!

Dan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.