I was doing some work recently which required DAX calculations to convert values from a specific base to another, e.g. to convert a value in hexadecimal format to a base 10 representation (or decimal), or from base 10 to binary.

Excel, like other languages has native functions to allow you to convert across base, such as the following :

- HEX2DEC – to convert a hexadecimal
- FF = 255

- DEC2BIN – to convert a decimal number to binary
- 255 = 1111 1111

- BIN2DEC – convert a binary number to decimal
- 1111 = 15

- DEC2HEX – convert a decimal number to hexadecimal
- 15 = F

- 15 = F

As part of that work, I created the following patterns that can be used to perform these types of conversions in DAX. The code snippets are pretty bare, so assumptions are made they are applied over clean data. This means there is no code to detect values like ‘X’ in a HEX2DEC conversion.

The code should be pretty straight forward to follow, and can easily be modified to work in a calculated column or measure.

Just assign the value you want to convert to the **ConvertMe **variable in each case and the final RETURN statement will convert as required.

You can create a calculated table to pre-process likely conversions required to help speed you up, and the patterns can easily be converted for other base-type conversions.

### HEX2DEC

EVALUATE VAR ConvertMe = "FF" VAR HEX2DEC = SUMX( // Loop control GENERATESERIES(0,LEN(ConvertMe)-1) , // Inner Calc VAR c = MID(ConvertMe,LEN(ConvertMe) - [value],1) VAR d = SWITCH(c,"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,int(c)) RETURN d * POWER(16,[value]) ) RETURN { HEX2DEC } // Returns 255

### DEC2BIN

EVALUATE VAR ConvertMe = 55 VAR BitTable = GENERATESERIES ( 1, 8 ) VAR DEC2BIN = CONCATENATEX ( BitTable, MOD ( TRUNC ( ConvertMe / POWER ( 2, [value] - 1 ) ), 2 ), , [Value], DESC ) RETURN { DEC2BIN } //Returns 001101111

### BIN2DEC

EVALUATE VAR ConvertMe = "0000101110" VAR BIN2DEC = SUMX ( // Loop control GENERATESERIES ( 0, LEN ( ConvertMe ) - 1 ), // Inner Calc VAR c = MID ( ConvertMe, LEN ( ConvertMe ) - [value], 1 ) RETURN c * POWER ( 2, [value] ) ) RETURN { BIN2DEC } // Returns 46

### DEC2HEX

EVALUATE VAR ConvertMe = 255 VAR Base = 16 VAR BitTable = GENERATESERIES ( 1, 8 ) VAR DEC2HEX = CONCATENATEX( BitTable, VAR c = MOD ( TRUNC ( ConvertMe / POWER ( base, [value] - 1 ) ),base ) RETURN SWITCH(c,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",c), , [Value],Desc) RETURN { DEC2HEX } // Returns 'FF'

This is probably not something you are likely to need for many reports. But it’s fun to work these out using basic maths.

I used some of these calculations in a Tic Tac Toe game I wrote in DAX last year. This time around, I was doing something much more business practical. 🙂

A PBIX file can be downloaded here with these calculations loaded into a calculated table.

Once you have converted your values to Binary, you can use this article to help include bitwise operations in your calculations.

Let me know what you think 🙂

Hi Phil,

“This is probably not something you are likely to need for many reports. But it’s fun to work these out using basic maths.”

I absolutely agree with the fun part. In addition you’ll learn a lot by using the simple elements of a language to solve these kind of puzzles.

It may not make much sense in practical scenarios ( or in your blog post for that matter ), however, negative integer numbers use two’s complement.

-55 is ‘11001001’ not ‘00110111’.

just thought I would mention it. 🙂

best regards Jes.

Great comment and you are right about the negative representation in binary. I’m sure it’s not too much of a tweak to the core pattern to produce the two’s compliment result. 🙂