05-01-2020 14:30 PM - last edited 05-01-2020 20:17 PM
In my recent quest to create or catalog as many DAX equivalents for Excel functions, there are a bevy of Excel functions that deal with binary data. So, it made sense to me start creating some of these binary functions that are missing from DAX, like DEC2BIN and now the reverse, BIN2DEC.
BIN2DEC = VAR __Binary = MAX('Table'[Binary]) VAR __Table = ADDCOLUMNS( GENERATESERIES(0,LEN(__Binary)-1,1), "Decimal",POWER(2,[Value]) * MID(__Binary,LEN(__Binary) - [Value],1) ) RETURN SUMX(__Table,[Decimal])
NOTE: The original Excel BIN2DEC function supports negative numbers. This DAX version does not. Why? Well, because I think negative binary numbers are kind of dumb and I cannot reconcile the way that Excel's BIN2DEC and DEC2BIN functions create "negative" binary numbers with other sources on this topic like here and here. But, on the plus side, this DAX version can handle binary numbers with more than 10 bits (511 decimal), which is a limitation of Excel's BIN2DEC function.