[Cloud release: 2025.01.15]
[On-prem release: 2025 January]
[On-prem build: 25.01.21002]
In the Data Model Editor, you can add calculations based on other measures - even using advanced features such as IIF statements.
The ROLAP calculation engine supports format strings from measures referenced in a calculation to "flow" up to the result of the expression, potentially making a calculation use different format strings based on the underlying measure(s).
E.g.
IIF(<something>, [Measure1], [Measure2])
will use the format string from Measure1 if <something> is true and the format string from Measure2 if <something> is false.
This requires that the calculation's own format string is not set. If the calculation's own format string is set, that one takes precedence.
Sometimes, it would be nice if you could specify a format string directly in the calculation expression. It could be simply done by adding the format string in quotes separated by a colon from the actual calculation:
IIF([Measure1] < 10000, [Measure1]:"#,##0.00", [Measure1]:"#,##0")
This will make any numbers below 10000 be formatted with two decimals and otherwise without any decimals. Of course, if the calculation has a format string configured directly, the format strings in the expressions will be overridden. Observe that the measure referenced is always the same, so you cannot rely on the format string from the measure if you want to do this.
This would make it easier to make a calculation that sometimes display an actual value and sometimes displays a percentage - e.g. something like this:
IIF(ISNULL([Total]),[Actual],([Actual]/[Total])*100:"##0 %")
This expression would use Actual's format string if Total is null and just display Actual's value in that case. Otherwise, Actual is divided by Total and the percentage format string is used instead.
Comments
Please sign in to leave a comment.