Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
For a calculated column, I am told the following expression gives a circular dependency.
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.
Solved! Go to Solution.
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.
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.
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:
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:
(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.