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

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

The BITLSHIFT function

It’s time for another of the most commonly used functions on the planet.  If the planet is Uranus.  This function returns a number shifted left by the specified number of bits.  For those of us who have social lives, this means that you first convert the number to binary and then add on a specified number of zeros before converting the new binary number back to a decimal number.

The BITLSHIFT function employs the following syntax to operate:

The BITLSHIFT function has the following arguments:

• number: this is required, and must be any DAX expression that returns an integer expression
• shift_amount: this is also required.  The shift_amount must also be an integer.

It should be further noted that:

• shifting a number left is equivalent to adding zeros (0) to the right of the binary representation of the number.  For example, a two-bit shift to the left on the decimal value 4 converts its binary value (100) to 10000, or 16 in decimal
• there is no limit on number, but the result may underflow / overflow
• if the absolute value of shift_amount is greater than 64, BITLSHIFT will return no error, but it will result in an underflow / overflow
• a negative number used as the shift_amount argument shifts the number of bits to the right
• a negative number used as the shift_amount argument returns the same result as a positive shift_amount argument for the BITRSHIFT function.

Please see my example below:

In binary, 2 is 10, so this becomes 10000, which is 16 in decimal.

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.