topic Re: Use a slicer to apply conditional formatting on a chart in DAX Commands and Tips

Use a slicer to apply conditional formatting on a chart

Samp17 — Tue, 19 Mar 2024 15:32:08 GMT

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.

Re: Use a slicer to apply conditional formatting on a chart

Daoud_H — Tue, 19 Mar 2024 15:47:15 GMT

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.

Re: Use a slicer to apply conditional formatting on a chart

Samp17 — Tue, 19 Mar 2024 15:55:27 GMT

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:

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

Re: Use a slicer to apply conditional formatting on a chart

Daoud_H — Tue, 19 Mar 2024 16:01:55 GMT

Try this instead :

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

Re: Use a slicer to apply conditional formatting on a chart

Samp17 — Tue, 19 Mar 2024 16:20:12 GMT

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.

Re: Use a slicer to apply conditional formatting on a chart

Samp17 — Tue, 19 Mar 2024 16:48:14 GMT

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