PowerBI : dynamic dimension in chart : calculated column does not update when input parameter is modified in slicer

Question

pbix file : https://drive.google.com/file/d/1x6u9a1vamiWnaTgJY-yYTIpANlz2FXyn/view?usp=sharing

I want to enable the user to do a simulation in PBI desktop. A color is associated to an ID depending on the risk score of the ID and a threshold the user can play with in the report. The user should be able to see how the amount of IDs per color changes depending on the threshold chosen.

Screenshot of Data tab

screenshot of report tab

I have tried using a calculated column, but as RADO pointed out, calculated columns cannot be impacted by filter changes, measures or parameters. I tried a measure which updates fine, but I cannot figure how to use this "color" measure as a axis in the plot I am trying to make.

Answer 1

A small note first: I renamed your 'Table' into 'Data', since 'Table' is a reserved name; I also used my own names for the measures, change them as necessary.

Create a dimension table for score colors:

Score Colors = { "Red", "Green" }

(it's similar to the Threshold table - a disconnected table).

Create a measure for total amount:

Total Amount = SUM( Data[Amount] )

Create a measure that defines score color. A nuance: you should be careful about what you want to see in the totals. I chose not to show color in the totals as it makes no sense.

Score Color =
VAR Selected_Threshold = [Threshold Value]
VAR Current_Score = SELECTEDVALUE ( Data[Risk score] )
VAR Result = IF ( Current_Score > Selected_Threshold, "Green", "Red" )
RETURN
    IF ( NOT ISBLANK ( Current_Score ), Result )

Finally, create a measure that calculates score amount by color dynamically:

Dynamic Chart =
VAR Current_Color = SELECTEDVALUE ( 'Score Colors'[Color] )
VAR Result =
    SUMX (
        VALUES ( Data[ID] ),
        IF ( [Score Color] = Current_Color, [Total Amount] )
    )
RETURN
    Result

How it works: we iterate over the list of IDs, compute their score colors, and compare to the color on the chart axis. If they match, amount is added to the result.

Finally, create a chart with Color field from the table "Score Colors" on x-axis, and [Dynamic Chart] measure on y-axis:

Result: