pbir-conditional-formatting

## PBIR Conditional Formatting Reference Three approaches: measure-based (flexible), `FillRule` gradients (no DAX), and rule-based `Conditional` (UI-generated). ### Approach Matrix | Approach | Best For | Pros | Cons | |---|---|---|---| | Measure-based | Custom logic, theme colors | Full DAX, readable | Requires extension measure | | `linearGradient2` / `linearGradient3` | Color scales | No DAX | Gradients only | | `Conditional.Cases` | UI-portable rules | Power BI UI round-trips | Verbose | ### Supported Properties Not every property accepts measure expressions. These do: `fill`, `borderColor`, `defaultColor`, `fontColor`, `color`, `backgroundColor`, `lineColor`, `markerColor`, `strokeColor`, `text`, `titleText`, `fontSize`, `strokeWidth`, `weight`, `transparency`, `radius`, `url`, `good`, `bad`, `neutral`, `target`, `icon` Everything else is literal-only or `ThemeDataColor`. ### dataViewWildcard Selector The key to per-point formatting. | `matchingOption` | Behavior | |---|---| | `0` | Identities + totals (series-level) | | `1` | Per data point (most common for CF) | | `2` | Totals only | ```json "selector": {"data": [{"dataViewWildcard": {"matchingOption": 1}}]} ``` Wrong for per-point: `"selector": {"metadata": "Sales.Revenue"}` — evaluates once per series. ### Two-Entry Array Pattern (Required) `dataPoint`, `lineStyles`, `error` require a two-entry array: base entry + conditional entry. ```json "dataPoint": [ {"properties": {}}, { "properties": { "fill": {"solid": {"color": {"expr": {"Measure": { "Expression": {"SourceRef": {"Schema": "extension", "Entity": "_Formatting"}}, "Property": "Bar Color" }}}}} }, "selector": {"data": [{"dataViewWildcard": {"matchingOption": 1}}]} } ] ``` ### Extension Measure (for color CF) Defined in `Report.Report/definition/reportExtensions.json`. **`dataType` MUST be `"Text"`** for color measures. Must attach to an EXISTING entity from the model. ```json { "name": "extension", "entities": [{ "name": "_Formatting", "measures": [{ "name": "Bar Color", "dataType": "Text", "expression": "SWITCH(TRUE(), [Value]<10, \"bad\", [Value]<50, \"neutral\", \"good\")" }] }] } ``` Theme color names returned by measures: `"bad"`, `"good"`, `"neutral"`, `"minColor"`, `"midColor"`, `"maxColor"`. ### Measure Reference in CF Expressions ```json {"expr": {"Measure": { "Expression": {"SourceRef": {"Schema": "extension", "Entity": "_Formatting"}}, "Property": "Bar Color" }}} ``` `"Schema": "extension"` is required for report-level extension measures. Omit for model measures. ### Pattern — Bar / Column Fill ```json "dataPoint": [ {"properties": {}}, { "properties": { "fill": {"solid": {"color": {"expr": {"Measure": { "Expression": {"SourceRef": {"Schema": "extension", "Entity": "_Formatting"}}, "Property": "Bar Color" }}}}} }, "selector": {"data": [{"dataViewWildcard": {"matchingOption": 1}}]} } ] ``` ### Pattern — linearGradient2 (Two-Color) Data-driven (scale to observed range): ```json {"FillRule": {"linearGradient2": { "min": {"color": {"Literal": {"Value": "'minColor'"}}}, "max": {"color": {"Literal": {"Value": "'maxColor'"}}}, "nullColoringStrategy": {"strategy": {"Literal": {"Value": "'asZero'"}}} }}} ``` Explicit bounds (fixed thresholds): ```json {"FillRule": {"linearGradient2": { "min": {"color": {"Literal": {"Value": "'minColor'"}}, "value": {"Literal": {"Value": "0D"}}}, "max": {"color": {"Literal": {"Value": "'maxColor'"}}, "value": {"Literal": {"Value": "1D"}}}, "nullColoringStrategy": {"strategy": {"Literal": {"Value": "'asZero'"}}, "color": {"Literal": {"Value": "'#FFFFFF'"}}} }}} ``` ### Pattern — linearGradient3 (Diverging) ```json {"FillRule": {"linearGradient3": { "min": {"color": {"Literal": {"Value": "'minColor'"}}, "value": {"Literal": {"Value": "-1D"}}}, "mid": {"color": {"Literal": {"Value": "'neutral'"}}, "value": {"Literal": {"Value": "0D"}}}, "max": {"color": {"Literal": {"Value": "'maxColor'"}}, "value": {"Literal": {"Value": "1D"}}}, "nullColoringStrategy": {"strategy": {"Literal": {"Value": "'asZero'"}}} }}} ``` ### Pattern — Line Segment Colors (Single-Series Only) Multi-series line charts cannot use segment coloring. Measure must return hex. ```json "lineStyles": [ {"properties": {"segmentGradient": {"expr": {"Literal": {"Value": "true"}}}}}, { "properties": { "strokeColor": {"solid": {"color": {"expr": {"Measure": { "Expression": {"SourceRef": {"Schema": "extension", "Entity": "_Formatting"}}, "Property": "Line Color" }}}}} }, "selector": {"data": [{"dataViewWildcard": {"matchingOption": 1}}]} } ] ``` `segmentGradient` only affects `strokeColor`. It does NOT work with `lineStyle`, `strokeWidth`, or `markerFill`. ### Pattern — Axis Label Colors No selector needed. ```json "categoryAxis": [{ "properties": { "labelColor": {"solid": {"color": {"expr": {"Measure": { "Expression": {"SourceRef": {"Schema": "extension", "Entity": "_Formatting"}}, "Property": "Axis Color" }}}}} } }] ``` ### Pattern — Marker Transparency (0-100) Marker color / shape / size do NOT support CF. Transparency does. ```json "markers": [ {"properties": {"borderShow": {"expr": {"Literal": {"Value": "false"}}}}}, { "properties": { "transparency": {"expr": {"Measure": { "Expression": {"SourceRef": {"Schema": "extension", "Entity": "_Measures"}}, "Property": "Marker Opacity" }}} }, "selector": {"data": [{"dataViewWildcard": {"matchingOption": 1}}]} } ] ``` ### Pattern — Rule-Based Conditional ```json {"expr": {"Conditional": {"Cases": [ {"Condition": {...}, "Value": {"Literal": {"Value": "'#FF0000'"}}}, {"Condition": {...}, "Value": {"Literal": {"Value": "'#00FF00'"}}} ]}}} ``` Cases evaluated in order; first match wins. ### ComparisonKind Codes | Value | Operator | |---|---| | `0` | Equal | | `1` | Greater than | | `2` | Greater than or equal | | `3` | Less than or equal | | `4` | Less than | ### Logical Wrappers ```json {"And": {"Left": {...}, "Right": {...}}} {"Or": {"Left": {...}, "Right": {...}}} {"Not": {"Expression": {...}}} ``` ### ScopedEval + AllRolesRef (Global Context) Removes filter context for global min/max: ```json {"ScopedEval": {"Expression": {"Measure": {...}}, "Scope": [{"AllRolesRef": {}}]}} ``` ### Data Bars (table/matrix columns) ```json "dataBars": { "positiveColor": {"solid": {"color": {"expr": {"Literal": {"Value": "'#E1EBF2'"}}}}}, "negativeColor": {"solid": {"color": {"expr": {"Literal": {"Value": "'#E5B97D'"}}}}}, "axisColor": {"solid": {"color": {"expr": {"Literal": {"Value": "'#FFFFFF'"}}}}}, "reverseDirection": {"expr": {"Literal": {"Value": "false"}}},