Hello, I have a report where I am displaying all the attributes for a product. For each of the attribute I am calculating the usage. Based on the usage percentage, I have a formatting rule to display various colors. I have 40 attributes so I have 40 measures!. So if the client changes the range tomorrow then I need to update all 40 measures. Is there a way where I can store range in a global parameter or variable so that I need to change it in only one place

Any help is appreciated

Hello, I have a report where I am displaying all the attributes for a product. For each of the attribute I am calculating the usage. Based on the usage percentage, I have a formatting rule to display various colors. I have 40 attributes so I have 40 measures!. So if the client changes the range tomorrow then I need to update all 40 measures. Is there a way where I can store range in a global parameter or variable so that I need to change it in only one place

Any help is appreciated

• powerbi
• powerbi-desktop

1 Answer 1

You can use a Parameter Table.

Step 1: Create a Parameter Table go to Modeling > New Table and create a table like this:

 FormattingRanges = DATATABLE(
    "RangeName", STRING,
    "MinValue", INTEGER,
    "MaxValue", INTEGER,
    "ColorCode", STRING,
    {
        { "Low", 0, 30, "Red" },
        { "Medium", 31, 70, "Yellow" },
        { "High", 71, 100, "Green" }
    }
)

Step 2: Create a Measure for Dynamic Formatting

    Dynamic_Color =
VAR UsageValue = SELECTEDVALUE(YourTable[Usage]) -- Adjust based on your field
VAR Color = 
    LOOKUPVALUE(
        FormattingRanges[ColorCode], 
        FormattingRanges[MinValue], 
        MAXX(FILTER(FormattingRanges, UsageValue >= FormattingRanges[MinValue] && UsageValue <= FormattingRanges[MaxValue]), FormattingRanges[MinValue])
    )
RETURN
Color

Lets asume we have a Table with Values Like this:

select your visual

Go to Format > Visual > Expand "Cell elements"

Choose the column where you want to apply formatting.

Find "Background color" or "Font color". Set "Format style" to "Field value". In "Based on field," select your measure (e.g., Dynamic_Color).

Then it should look like this: