DAX Bitwise Functions in Power BI

Bitwise functions in DAX allow users to perform logical operations on the binary representation of integers. While bitwise functions are often underused, they are incredibly effective in situations involving flags, encoded data, and other lower-level data analysis tasks. They are fast-performing and help in avoiding complex logic in DAX formulas. In this blog, you will explore DAX Bitwise functions with their syntax and some detailed examples.

Table of Contents:

• What are DAX Bitwise Functions in Power BI?
• Advantages of using Bitwise functions in Power BI
• Types of Bitwise Functions in DAX
  1. BITAND Function in DAX
  2. BITOR Function in DAX
  3. BITXOR Function in DAX
  4. BITLSHIFT Function in DAX
  5. BITRSHIFT Function in DAX
• Summary Table
• Conclusion

What are DAX Bitwise Functions in Power BI?

Bitwise operations are basic logical operations that operate on bits of integers. When you use a bitwise function in DAX (Data Analysis Expressions), you are working with numbers in their binary form. Numbers are binary representations as sequences of bits (0s and 1s), and bitwise functions directly operate on the bits of the numbers. This becomes particularly relevant and effective to consider when using an integer (for instance) to store multiple pieces of information, like in flags using bits.

Many data modeling fields use binary flags or status codes stored as integers. Bitwise operations are a great way to extract, compare, or modify a bit efficiently.

Advantages of using Bitwise functions in Power BI

• Compact Storage: Combine multiple boolean or flags into a single integer column, taking up less space and simplifying your schema.
• Efficient Computation: Execute computations or algorithms significantly faster than performing condition checks on every column.
• Flexible Filtering: Easily extract and focus on individual conditions stored as bits within a single numeric column, creating more unit tests, making your queries efficient, and directly on target.
• Data Decoding: Decode packed binary data, especially when you are working with imported data from legacy or outside systems.
• Data Modelling: Reduce the number of columns in a data model by combining multiple binary states.

Visualize, Analyze, Succeed – Learn Power BI the right way!

Enroll now and get job-ready faster.

Explore Program

Types of Bitwise Functions in DAX

DAX provides five primary bitwise functions:

• BITAND: Bitwise AND operation
• BITOR: Bitwise OR operation
• BITXOR: Bitwise XOR operation
• BITLSHIFT: Bitwise left shift
• BITRSHIFT: Bitwise right shift

Each function offers unique logical manipulation and can be chained or combined for more complex operations.

We will use the following dataset to perform the bitwise functions in Power BI:

Example:

1. BITAND Function in DAX

The BITAND function performs a binary logical AND between two integers. It is useful for determining which flags/features are common between pairs of sets of data. The problem usually arises when there are many binary values crammed into one column. You can determine the overlap between those two integers using a simple approach where you compare every bit position. This can be particularly useful for filtering or validation when a particular combination of flags is present.

Syntax:

BITAND(<number1>, <number2>)

Example:

BITAND_Result = BITAND('Table'[Number1], 'Table'[Number2])

Steps:

• Convert 6 to binary: 0110
• Convert 3 to binary: 0011
• Perform AND operation: 0010, which is 2 in decimal

Output:

Explanation: Here, this query is used to return the result of the bitwise AND operation between Number1 and Number2 for each row in the dataset.

Note: Make sure to replace ‘Table’ with the actual table name you are using in Power BI.

2. BITOR Function in DAX

The BITOR function performs a logical OR operation between two integers – it compares each bit against the other integer and will return 1 if either of the inputs has a 1 in that position. BITOR is practical for merging flags or statuses, aiding in cases where a combined set of features or permissions is needed. It is a simple way to combine information from multiple sources. It simplifies the evaluation of a combination of binary values.

Syntax:

BITOR(<number1>, <number2>)

Example:

BITOR_Result = BITOR('Table'[Number1], 'Table'[Number2])

Steps:

• Convert 6 to binary: 0110
• Convert 3 to binary: 0011
• Perform OR operation: 0111, which is 7 in decimal

Output:

Explanation: Here, this query is used to return the result of the bitwise OR operation between Number1 and Number2 for each row.

3. BITXOR Function in DAX

BITXOR means bitwise exclusive or. It will return 1 if the bits in both inputs are different. It can be very useful for identifying changes or mismatches between two binary status versions. If two items are identical, the XOR of them is 0. It can show differences very quickly regarding flag settings. This function is especially useful in the area of summary comparison and version control.

Syntax:

BITXOR(<number1>, <number2>)

Example:

BITXOR_Result = BITXOR('Table'[Number1], 'Table'[Number2])

Steps:

• Convert 6 to binary: 0110
• Convert 3 to binary: 0011
• Perform XOR operation: 0110 XOR 0011 = 0101, which is 5 in decimal.

Output:

Explanation: This will return the result of the bitwise XOR operation between Number1 and Number2.

4. BITLSHIFT Function in DAX

BITLSHIFT shifts the bits of a number to the left by the specified shift amount. Each left shift multiplies the number by 2 for that shift. This provides functionality to dynamically create new flags, as well as scale binary values. BITLSHIFT can be used to reserve bit positions for later use as well. This function is an essential tool for bitwise calculations in cases when flags might need to be aligned for counting.

Syntax:

BITLSHIFT(<number>, <shift_count>)

Example:

BITLSHIFT_Result = BITLSHIFT('Table'[Number1], 2)

Steps:

• Convert 6 to binary = 0110
• Left shift by 2 = 11000, which is 24 in decimal

Output:

Explanation: Here, this query will return the output having Number1 shifted by two positions to the left.

5. BITRSHIFT Function in DAX

BITRSHIFT right shifts the binary representation of a number by a given count, which is equivalent to dividing the number by powers of two. This operation is often performed on flags to deconstruct the flag by removing lower-order bits from the right side of the number. You can simplify the encoded data structures with this function.

Syntax:

BITRSHIFT(<number>, <shift_count>)

Example:

BITRSHIFT(<number>, <shift_count>)

Steps:

• Convert 20 into binary = 010100
• Right shift by 2 = 0101, which is 5 in decimal.

Output:

Explanation: Here, this query will return the output having Number1 shifted by two positions to the right.

Summary Table

Conclusion

Bitwise functions in DAX provide a small, efficient means of managing and analysing flags or binary data. They allow you to perform more detailed and performance-oriented checks with status or condition codes as integers. When used properly, they help simplify data models, improve speed, and give better insights from complex data. Understanding their potential and combining them with filtering logic will enhance your work in data cleansing, analysis, and interpretation, making your results more accurate and efficient.

To learn more about Power BI and its functions, check out this Power BI Course and also explore Power BI Interview Questions prepared by industry experts.

DAX Bitwise Functions in Power BI- FAQs