I faced the same issue so I created a Tabular Editor script that creates a new conditional formatting field.
This script allows to loop over the selected measures or loop over all the measures in the model, in this latter case, I added a condition on the measure name to only apply the script to meaures with "YOY" in their name.
// Creates a measure used as field value for rule-based conditional formatting for each selected measure
foreach(var m in Selected.Measures) { // Loop over selected measures
// foreach(var m in model.AllMeasures) { // Loop over all the measures in the model
var newMeasureName = "__fontColor_" + m.Name;
// Check if measures already exists + condition on measure name
if (!Model.Tables["__UI_measures"].Measures.Contains(newMeasureName) && m.Name.Contains("YOY")) {
// m.Table.AddMeasure( // Add new measure to the same table
Model.Tables["__UI_measures"].AddMeasure( // Add measure in a specific table
"__fontColor_" + m.Name, // New measure name pattern based on base measure name
@"VAR __deltaPlusColor = LOOKUPVALUE(
Colors[Color],
Colors[Name],
""deltaPlusColor""
)
VAR __deltaMinusColor = LOOKUPVALUE(
Colors[Color],
Colors[Name],
""deltaMinusColor""
)
VAR __deltaSameColor = LOOKUPVALUE(
Colors[Color],
Colors[Name],
""deltaZeroColor""
)
RETURN
SWITCH(
TRUE(),
" + m.DaxObjectName + @" > 0, __deltaPlusColor,
" + m.DaxObjectName + @" = 0, __deltaZeroColor,
" + m.DaxObjectName + @" < 0, __deltaMinusColor
)",
"Formatting" // to create the new measure in a specific display folder
// m.DisplayFolder // to create the new measure in the same display folder
);
}
}
Here's the DAX code created by this script, you can adapt it to your use case in the script.
fontColor_ADR YOY% =
VAR __deltaPlusColor = LOOKUPVALUE(
Colors[Color],
Colors[Name],
"deltaPlusColor"
)
VAR __deltaMinusColor = LOOKUPVALUE(
Colors[Color],
Colors[Name],
"deltaMinusColor"
)
VAR __deltaZeroColor = LOOKUPVALUE(
Colors[Color],
Colors[Name],
"deltaZeroColor"
)
RETURN SWITCH(
TRUE(),
[ADR YOY%] > 0,
__deltaPlusColor,
[ADR YOY%] = 0,
__deltaZeroColor,
[ADR YOY%] < 0,
__deltaMinusColor
)
I'm using LOOKUP to avoid hard coding the colors HEX codes. I set all the colors I needed in a table. Just replace the LOOKUPs with the HEX code if you don't want to create a separate table.
Colors =
UNION(
ROW("Name", "deltaPlusColor", "Color", "#1856C9"),
ROW("Name", "deltaMinusColor", "Color", "#B80040"),
ROW("Name", "deltaZeroColor", "Color", "Black")
)
As a side note, I recommend using ChatGPT and Claude to adapt the script to your use case, or for creating a new one from scratch. They are very good at it, and it's easy to experiment and iterate in Tabular Editor. 😉
I hope this helps.
