Power Pivot Principles: The A to Z of DAX Functions – FLOOR

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 FLOOR. 

The FLOOR function

The FLOOR function is a DAX function which returns the results as text, rounded down to the nearest multiple of significance.  It has the following syntax:

• number: this argument is required which is the number we want to round
• significance: this is also required.  This is the multiple to which you wish to round.  Both the number and significance arguments need to have the same sign (i.e. both positive or else both negative).

It should be noted that:

• if either argument is nonnumeric, the FLOOR function will return the #VALUE! error
• if number and significance arguments have different signs, the FLOOR function returns the #NUM! error
• a value is rounded down when it is adjusted away from zero, regardless of the sign of the number.  No rounding takes place if the quantity is an exact multiple of the significance
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Let’s consider the following example where we have the following TB_Grade table:

We can write the following DAX measure to round these grades to the nearest 0, 0.25, 0.50, 0.75 as follows:

This measure will return the following:

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.