Reply
KMZ_ESS
Helper I
Helper I
Partially syndicated - Outbound

Bubble size depending on account category and on last visit date

Dear all,

 

I want to create a map visual showing the accounts of our account managers and then:

1. highlight especially A and B accounts

2. highlight accounts that haven't been visited the last x months

 

regarding 1.: only using different colours for the different categories is not sufficient, because there are adresses with multiple accounts, so there would be one bubble with different colours. That's not a nice visualization.

Our particular dataset doesn't contain revenue data, so I have to use the account categories (could also use "potential" data, but the data quality curently is not good). As far as I can see I can't set different bubble sizes per category. Is there a measure I could use and enter in the bubble size field? (Also unfortunately, our security setup doesn't allow me to create additional columns). 

 

regarding 2.: I know there is no "NOT relative date" function, so I can't show accounts with a last visit date e.g. not in last 3 months. Having a fixed date (e.g. last visit date before 1.1.23) has limited utility, because at some point all A/B accounts will have been visited. But then it's possible that an A account was visited this year, but when it's e.g. August or later the last visit was 6 months ago, which would be too long ago and not visible if I set the date to 1.1.23. Does anyone have a suggestion how I could highlight accounts that haven't been visited in the last x months in a map? Can I create a measure that calculates how many days ago the last visit was and then dependent on this value determine the bubble size?

 

I hope this makes sense. Let me know in case of any questions. Thanks a lot for your help!


Best regards

Kaveh

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Syndicated - Outbound

Hi @KMZ_ESS 

 

Can you provide some sample data in table format? Do you have visit dates of every account in a table?

 

Based on the current information, you can try using a measure to give a number to every category for the bubble size. For example,

BubbleSize =
SWITCH (
    SELECTEDVALUE ( 'Table'[category] ),
    "category A", 1,
    "category B", 2,
    "category C", 3
)

 

Best Regards,
Community Support Team _ Jing

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Syndicated - Outbound

Hi @KMZ_ESS 

 

Can you provide some sample data in table format? Do you have visit dates of every account in a table?

 

Based on the current information, you can try using a measure to give a number to every category for the bubble size. For example,

BubbleSize =
SWITCH (
    SELECTEDVALUE ( 'Table'[category] ),
    "category A", 1,
    "category B", 2,
    "category C", 3
)

 

Best Regards,
Community Support Team _ Jing

Syndicated - Outbound

Hi @v-jingzhang,

 

thank you for getting back!

The measure works for categories A-D, but if I add "" or isblank for accounts that don't have a category it doesn't work anymore. How do I include these accounts in the measure?

 

Some accounts don't have an entry in "last visit date". See sample data below.

 

Account NameCategorisationLast Visit Date
Account1D 
Account2  
Account3D27/06/2022
Account4D 
Account5 23/06/2022
Account6D16/08/2022
Account7A05/05/2023
Account8D21/05/2021
Account9B24/05/2023
Account10C21/04/2023
Account11D07/07/2022
Account12B17/11/2022
Account13B05/06/2023
Account14A14/12/2022
Account15D 

Syndicated - Outbound

The blank issue is solved. The measure would be:

BubbleSize =
SWITCH (
    SELECTEDVALUE ( 'Table'[category] ),
    "category A", 1,
    "category B", 2,
    "category C", 3,
    0
)

 

For the last visit date I'm using a slicer (style: "before") for the time being, but I'm not sure this is the best solution. Leaving aside the issue with missing last visit date, if anyone has a suggestion for a better visualization I'm all ears. 🙂

KMZ_ESS_0-1687255476414.png

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)