Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I am currently looking to conditionally format text in a P&L. It is black for positive, red for negative. On top of it the shade of the text may change depending on the row to allow for differing background colours.
I have written a measure [Act SelectPeriod Text Colour] to determine the colour however it cannot be selected, returning the usual non-descript error in formatting as shown below:
The measure is as follows:
Act SelectPeriod Text Colour =
IF(
[Act SelectPeriod] < 0 ,
[Condensed P&L Text Colour (Red)],
[Condensed P&L Text Colour (Black)]
)
I have worked through all combinations of using the different measures involved and found the following:
- Substituting [Act SelectPeriod] for a hardcoded value in [Act SelectPeriod Text Colour] (above) does not change the result
- Using [Condensed P&L Text Colour (Red)] or [Condensed P&L Text Colour (Black)] work fine if used instead of [Act SelectPeriod Text Colour]
- Changing [Act SelectPeriod Text Colour] so it does not reference the two colour measures but has the code directly in the measure allows the formatting to work. Branching is critical so while this helps narrow down the issue it is not a viable solution.
- Although I cannot prove it, I feel this issue occured after calculation groups were introduced into the model. Applying (or not applying) calculation groups to the report/visual in question does not change the outcome of any of the testing.
An example of one of the colour measures is as follows.
Condensed P&L Text Colour (Black) =
IF(
ISINSCOPE('Reporting_P&L Structure'[P&L Consolidated Reporting Structure]),
SWITCH(
FIRSTNONBLANK('Reporting_P&L Structure'[Text Colour], TRUE),
"Black", "#000000",
"White", "#FFFFFF",
"Always White", "#FFFFFF",
"Light Grey", "#969696",
"Dark Grey", "#666666",
"#000000"
),
"#000000"
)
I'd appreciate any ideas for what could be the issue or what else I could test to resolve this.
If any of the information provided is unclear, please let me know. Unfortunately the model contains confidential data so cannot be shared, and would be quite difficult to reproduce a reasonably representative desensitised version.
Thanks.
Please explain again why you use a sub-measure to pick the color. If that measure returns BLANK() then your coloring rules will be ignored.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
98 | |
90 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |