Power Pivot Principles: The A to Z of DAX Functions – DOLLARFR
20 June 2023
In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at DOLLARFR.
The DOLLARFR function
The DOLLARFR function is one of the financial functions, used to convert a dollar price expressed as a decimal number into a dollar price expressed as an integer part and a fraction part. The fractional dollar numbers are sometimes used for security prices. It has the following syntax:
This function is essentially the opposite of the DOLLARDE function – it converts decimal numbers to fractional dollar numbers, such as securities prices. The DOLLARFR function has the following arguments:
- decimal_dollar: this is required and represents a decimal number
- fraction: this is required and represents the integer to use in the denominator of the fraction.
It should be further noted that:
- fraction is rounded to the nearest integer
- an error will return if 1 > fraction ≥ 0
- an error will return if fraction < 0
- the DOLLARFR function is not compatible with Power Pivot and currently it is only compatible with Power BI, SSAS Tabular, Azure AS and SSDT
- this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
As an example, imagine you wish to express 3.625 dollars as an integer and number of 16ths of a dollar. We could write the following DAX code:
This will result in:
The result above is 3.10 representing 3 whole dollars and 10/16 (.625) of a dollar.
Similar to the DOLLARDE function, the DOLLARFR function will display the same errors if:
- 1 > fraction ≥ 0:
- or fraction < 0:
Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.