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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Samp17
New Member

Use a slicer to apply conditional formatting on a chart

I have a table listing products and their properties that I would like to display in a scatter chart.

My objective is to use a Slicer to select one product to use as a 'reference' - I would like this one product to appear as a different marker color compared to the rest of the products in the scatter chart. 

 

So far, I have duplicated my product table, and assigned a slicer to this table so that I may select product without filtering the dataset for my chart.

My understanding is that I need to create a new measure (or new calculated column - unsure which), get the SELECTEDVALUE of the 'reference table with filter' and to compare it with the rows in my table for the chart data.  If equal, enter "YELLOW",  else enter "BLUE". 


I have tried with both a measure and a column, but both give me errors I do not understand.

 

For a Measure I am told that a single value for column 'Product' in 'AllProducts' cannot be determined. 

Marker color = IF (SELECTEDVALUE('Reference'[Product]) == ('AllProducts'[Product]), "Yellow", "Blue")


For a calculated column, I am told the following expression gives a circular dependency.

Marker color = IF (SELECTEDVALUE('Reference'[Product]) == ('AllProducts'[PRODUCT]), "Yellow", "Blue")


Ultimately, I then want to create a formatting rule for the marker based on this 'color column' where I can map a value "Yellow" to a color. 

Can anybody help me progress as I have been working on this issue for several hours? Thanks.

 

1 ACCEPTED SOLUTION
Samp17
New Member

If I use calculated column, I can get this to fill the values of a column as expected, however as this is static data and will not update when changing the slicer, this is not the solution for me. 
For context, the issue I was having is that I duplicated the data using Reference = 'AllProducts' and I believe this was crating the circular dependency error I saw before. 

Measures do not have row context unless used with specific functions (all of which are not applicable here). 
However, I do not need to calculate the full column at once, only to pass the current data to my measure when we need a result. 


IsSelectedProduct =
IF (
    SELECTEDVALUE('Reference'[Model]) = SELECTEDVALUE('L-Acoustics'[Model]),
    "Yellow",
    "Blue"
)
This evaluates the current selected row of my table data when needed and compares it to the selected product. 
Then I can create a conditional formatting rule based on this measure. If the result is 'Yellow' then display the marker as yellow

View solution in original post

5 REPLIES 5
Samp17
New Member

If I use calculated column, I can get this to fill the values of a column as expected, however as this is static data and will not update when changing the slicer, this is not the solution for me. 
For context, the issue I was having is that I duplicated the data using Reference = 'AllProducts' and I believe this was crating the circular dependency error I saw before. 

Measures do not have row context unless used with specific functions (all of which are not applicable here). 
However, I do not need to calculate the full column at once, only to pass the current data to my measure when we need a result. 


IsSelectedProduct =
IF (
    SELECTEDVALUE('Reference'[Model]) = SELECTEDVALUE('L-Acoustics'[Model]),
    "Yellow",
    "Blue"
)
This evaluates the current selected row of my table data when needed and compares it to the selected product. 
Then I can create a conditional formatting rule based on this measure. If the result is 'Yellow' then display the marker as yellow
Daoud_H
Helper I
Helper I

Hi @Samp17,

 

A more effective approach might involve using a measure that dynamically checks the slicer selection and then applying conditional formatting based on this measure.

 

Instead of a calculated column, create a measure that checks if the current row's product matches the selected product in the slicer.

 

 

IsSelectedProduct = 
IF (
    SELECTEDVALUE('Reference'[Product]) = 'AllProducts'[Product],
    "Yellow",
    "Blue"
)

 

 

 

Then use this measure to apply conditional formatting to your scatter chart.

In Power BI, you can do this by:

  • Selecting your scatter chart.
  • Going to the Format pane.
  • Navigating to the Data colors section.
  • Clicking on the "Color based on" dropdown and selecting your measure (IsSelectedProduct).
  • Mapping the values "Yellow" and "Blue" to the desired colors.

 

Hope it helps.

This confirms I am on the right path. 
I have tried again creating the measure with your expression above and I am faced with this issue:

Samp17_0-1710863660235.png

(Apologies, the column name was not 'product' as previously mentioned, it is 'Model')


Try this instead : 

IsSelectedProduct = 
VAR SelectedProduct = 
    CALCULATE(
        MAX('Reference'[Model]),
        ALLSELECTED('Reference'[Model])
    )
RETURN
    IF (
        'AllProducts'[Model] = SelectedProduct,
        "Yellow",
        "Blue"
    )

Sadly I have exactly the same error message shown in this case too. 

I have found the following article which explains that measures do not have row context. 
https://www.sqlbi.com/articles/row-context-in-dax/

I am not sure where to go from here though.

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Top Solution Authors