Modern data visualization tools like PowerBI have revolutionized the way businesses analyze their data. With its expansive functionalities, it's possible to perform advanced calculations directly within PowerBI using its DAX (Data Analysis Expressions) language, even for tasks traditionally reserved for more heavy-duty data processing tools. One such advanced but essential task is computing the Network Classless Inter-Domain Routing (CIDR) from an IP address and subnet mask.
This article will guide you through creating a calculated column in PowerBI using DAX to compute the network CIDR, using a specific machine/network IP and mask. We'll break down the code, explain its components, and provide you with the understanding needed to adapt it for your solutions. Let's begin.
Understanding the Basics: IP Addresses and CIDR
Before diving into the DAX formula, let's briefly cover the basics of IP addresses and CIDR notation.
An IP address is a unique identifier assigned to each device connected to a computer network. It uses a 32-bit address space conventionally represented as four decimal numbers separated by periods, each part ranging from 0 to 255. For example, "192.168.1.1".
CIDR notation, however, is a method for specifying IP addresses and their associated routing prefix. It's a compact representation of an IP address and its associated network mask. For example, the CIDR notation "192.168.1.0/24" represents the IP address range from "192.168.1.0" to "192.168.1.255".
Calculating Network CIDR with DAX
To compute the network CIDR, we need to perform a bitwise AND operation between the IP address and the subnet mask, then determine the network address and the prefix length.
The following DAX formula provides a step-by-step computation:
[cidr_net] =
VAR mask = \"255.255.255.224\"
VAR dot1 = FIND ( \".\", mask, 1, 0 )
VAR p1 = VALUE ( IF ( dot1 > 0, ( MID ( mask, 1, dot1 - 1 ) ), \"0\" ) )
VAR dot2 = FIND ( \".\", mask, dot1 + 1, 0 )
VAR p2 = VALUE ( IF ( dot2 > 0, MID ( mask, dot1 + 1, dot2 - 1 - dot1 ), \"0\" ) )
VAR dot3 = FIND ( \".\", mask, dot2 + 1, 0 )
VAR p3 = VALUE ( IF ( dot3 > 0, MID ( mask, dot2 + 1, dot3 - 1 - dot2 ), \"0\" ) )
VAR p4 = VALUE ( IF ( dot3 > 0, MID ( mask, dot3 + 1, LEN ( mask ) - dot3 ), \"0\" ) )
VAR ip = \"10.10.1.38\"
VAR d1 = FIND ( \".\", ip, 1, 0 )
VAR ip1 = VALUE ( IF ( d1 > 0, ( MID ( ip, 1, d1 - 1 ) ), \"0\" ) )
VAR d2 = FIND ( \".\", ip, d1 + 1, 0 )
VAR ip2 = VALUE ( IF ( d2 > 0, MID ( ip, d1 + 1, d2 - 1 - d1 ), \"0\" ) )
VAR d3 = FIND ( \".\", ip, d2 + 1, 0 )
VAR ip3 = VALUE ( IF ( d3 > 0, MID ( ip, d2 + 1, d3 - 1 - d2 ), \"0\" ) )
VAR ip4 = VALUE ( IF ( d3 > 0, MID ( ip, d3 + 1, LEN ( ip ) - d3 ), \"0\" ) )
VAR ipAddress = ( ( ip1 * 256 + ip2 ) * 256 + ip3 ) * 256 + ip4
VAR ipMask = ( ( p1 * 256 + p2 ) * 256 + p3 ) * 256 + p4
VAR unmask = 2 ^ 32 - ipMask
VAR ipNet = INT ( ipAddress / unmask ) * unmask
VAR in1 = INT ( ipNet / 2 ^ 24 )
VAR in2 = INT ( MOD ( ipNet, 2 ^ 24 ) / 2 ^ 16 )
VAR in3 = INT ( MOD ( ipNet, 2 ^ 16 ) / 2 ^ 8 )
VAR in4 = MOD ( ipNet, 2 ^ 8 )
VAR Result = in1 & \".\" & in2 & \".\" & in3 & \".\" & in4 & \"/\" & 32 - LOG ( unmask, 2 )
RETURN Result
This DAX script consists of various steps, including:
- Breaking down the IP address and mask into their respective octets.
- Converting the octets into their bitwise integer form.
- Calculating the masked network address by applying bitwise AND on the IP and subnet mask.
- Converting the network address back to dotted decimal form.
- Calculating the CIDR prefix.
Why Might SQL or Power Query Be a Better Solution?
While DAX is powerful and flexible, it operates row-by-row, which might not be the most efficient for large datasets or complex calculations. SQL or Power Query, being set-based operations, can handle such computations more efficiently. Nevertheless, for specific, targeted analyses within PowerBI reports, this DAX solution is invaluable.