Highlight bubble in a Scatter chart based of Slicer

[Anonymous]

I have read a lot of Highlight in a chart but none addresses the issue I am having.
I have a table called Project and also created a disconnected table called SelectedProject, consist of Projectname.
In the Project table, I have two measure to use for Scatter visual and they are called SPI and CPI and columns name called RAG, projectname and ProjectAmount
In the scatter chart, CPI is on the x-axis and SPI on the y axis and Projectamount is on te Size. The RAG has 3 colour values (RED, GREEN, AMBER) and I used this to format the colour of the bubbles.
The Issue I am having is that how can I highlight a bubble when a projectname is selected from the slicer and when highlighted it should retain the RAG colour and other bubble still retain there RAG colour but faded on the background.

Project table looks like below

ProjectID	ProjectName	RAG	Projectamount	CPI	SPI
1	LandPro	RED	120	0.9	0.6
2	Glutter	RED	200	0.5	0.8
3	StreetFill	GREEN	320	0.1	0.7
4	Roof	AMBER	555	0.3	0.6
5	Windows setting	AMBER	560	0.7	0.5
6	Tile Purchase	GREEN	700	1.1	0.4
7	Other Items	RED	520	1.5	0.3

How can I achieve this using the above table?

Thanks

[v-jianpeng-msft · Accepted solution]

Hi, @Anonymous 

Thank you very much for your reply. This is mainly due to the fact that the AMBER in your original RAG column is not being recognized correctly. Because amber is a non-standard color in Power BI. Others, such as GREEN and RED, can be correctly identified. Here's how to improve:

BubbleColor1 = 
SWITCH(
    TRUE(),
    [IsSelected1] = 1, IF(VALUES(Project1[ProjectName]) IN SELECTCOLUMNS('SeletedProject1','SeletedProject1'[ProjectName]) ,
    SWITCH(TRUE(),
        [RAG2] = "RED",  "red",
        [RAG2] = "GREEN", "green",
        [RAG2] = "AMBER", "hsla(43,100%,50%,1)",
        "black"  -- Default color for other cases
    )
        ,
        SWITCH([RAG2],
            "RED","hsla(0,100%,80%,0.4)",
            "GREEN","hsla(0,100%,80%,0.4)",
            "AMBER","hsla(45,100%,50%,0.4)"
    
    )
    ),
    [RAG2] = "RED",  "red",
    [RAG2] = "GREEN", "green",
    [RAG2] = "AMBER", "hsla(43,100%,50%,1)",
    "black"  -- Default color for other cases
)

Here are the results:

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianpeng-msft]

Hi, @Anonymous 

Based on your description, I have created the two tables you said as follows:

I've created two metrics using the following DAX expression to determine if the current slicer is selected and to calculate the color for the current item.

IsSelected = IF(ISFILTERED('SelectedProject'[ProjectName]), 1, 0)

RAG1 = CALCULATE(SELECTEDVALUE(Project[RAG]),'Project'[Projectamount]=SELECTEDVALUE(Project[Projectamount])) 

Then I created a color gradient along with the highlighted metrics as follows:

BubbleColor = 
SWITCH(
    TRUE(),
    [IsSelected] = 1, IF(VALUES(Project[ProjectName]) IN SELECTCOLUMNS('SelectedProject','SelectedProject'[ProjectName]) ,[RAG1],
        SWITCH([RAG1],
            "RED","hsla(0,100%,80%,0.4)",
            "GREEN","hsla(0,100%,80%,0.4)",
            "AMBER","hsla(240,100%,80%,0.4)"
    
    )
    ),
    [RAG1] = "RED",  "red",
    [RAG1] = "GREEN", "green",
    [RAG1] = "AMBER", "AMBER",
    "black"  -- Default color for other cases
)

After that, you need to apply it in the conditional formatting of the bubble map:

Then create a slicer and select the corresponding items, which will show the original color, and the other items will have their original color. Here are the results:

Unchecked Status:

Project Selected Status:

Although it is not possible to adjust the size of each bubble using a DAX expression, it is possible to adjust the color gradient of the bubble using the DAX. I've provided the PBIX used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your reply.
It didn't work because I didn't explain properly.
I have tried to attach sample data but was blocked.
Let me explain better this time.

ProjectID	ProjectName	RAG	Projectamount	BCWP	ACWP	BCWS
1	LandPro	RED	120	4500	3500	5000
2	Glutter	RED	200	6330	5000	9500
3	StreetFill	GREEN	320	8222	4504	10000
4	Roof	AMBER	555	5000	2500	5200
5	Windows setting	AMBER	560	4504	2055	3990
6	Tile Purchase	GREEN	700	5500	3600	7000
7	Other Items	RED	520	6552	4500	6900

CPI and SPI are measures and are calcluated as follows:
CPI = Divide(Sum(Project[ACWP]), Sum(Project[BCWS]))
SPI = Divide(Sum(Project[BCWP]), Sum(Project[BCWS]))

This measure are put CPI in x-axis and SPI in y_axis, ProjectName in the Values and Projectamount in Size as shown below

The RAG is used to colour the bubbles and there is a slicer called ProjectName.
So measures are used not columns.

[v-jianpeng-msft]

Hi, @Anonymous 

Thank you very much for your reply. Based on the latest dataset you provided, I have created the following two tables:
project1:

SeletedProject1:

I created the following two measures: CPI and SPI:

In addition, I have created the following measures:

The bubble chart settings are as follows:

The slicer settings are as follows:

Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your quick response. It is working but one issue is that when I select Windows setting from the ProjectNamename slicer the bubble colour is blue instead of Amber

How can you help fix this?

[v-jianpeng-msft]

Hi, @Anonymous 

Thank you very much for your reply. You can change the color value in the hsla parentheses in the DAX expression. The following is an amber color value reference:

hsla(45, 100%, 50%, 1)

 You can tweak your colors more finely from the following websites:

HTML HSL and HSLA Colors (w3schools.com)

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your response. I have changed the HSL or HSLA colour for Amber but it is still showing blue colour when Windows setting and Roof are selected individually. see screenshot below

[v-jianpeng-msft · Accepted solution]

Hi, @Anonymous 

Thank you very much for your reply. This is mainly due to the fact that the AMBER in your original RAG column is not being recognized correctly. Because amber is a non-standard color in Power BI. Others, such as GREEN and RED, can be correctly identified. Here's how to improve:

BubbleColor1 = 
SWITCH(
    TRUE(),
    [IsSelected1] = 1, IF(VALUES(Project1[ProjectName]) IN SELECTCOLUMNS('SeletedProject1','SeletedProject1'[ProjectName]) ,
    SWITCH(TRUE(),
        [RAG2] = "RED",  "red",
        [RAG2] = "GREEN", "green",
        [RAG2] = "AMBER", "hsla(43,100%,50%,1)",
        "black"  -- Default color for other cases
    )
        ,
        SWITCH([RAG2],
            "RED","hsla(0,100%,80%,0.4)",
            "GREEN","hsla(0,100%,80%,0.4)",
            "AMBER","hsla(45,100%,50%,0.4)"
    
    )
    ),
    [RAG2] = "RED",  "red",
    [RAG2] = "GREEN", "green",
    [RAG2] = "AMBER", "hsla(43,100%,50%,1)",
    "black"  -- Default color for other cases
)

Here are the results:

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-jianpeng-msft  Thank you very much... I appreciate your knownledge and effort

[Anonymous · Accepted solution]

@v-jianpeng-msft Please, is there a way to ensure not only the Slicer that filter in scatter chart

So when clicked on Table or Funnel chart, this will perform the way in slicer is doing.
Click on any projectName on table or Funnel visual and Highlight the project selected on the scatter chart