DEC2BIN (Decimal to Binary) 05-01-2020 13:21 PM - last edited 05-01-2020 20:09 PM

[Greg_Deckler]

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 to create a base DAX measure to convert decimal numbers to their binary equivalents. I have seen some other decimal to binary formulas for DAX but I feel this version is superior to those as the others I have seen require you to have a predefined number of bits and some other minor flaws. This version tries to correct these flaws.

DEC2BIN = 
    VAR __DecimalNumber = MAX('Table'[Decimal])
RETURN
    IF(
        __DecimalNumber = 0,
        "0",
            VAR __Table = 
                ADDCOLUMNS(
                    GENERATESERIES(0,LOG(__DecimalNumber,2),1),
                    "Bit",MOD(TRUNC(__DecimalNumber / POWER(2,[Value])),2)
                )
        RETURN
            CONCATENATEX(__Table,[Bit],"",[Value],DESC)
    )

NOTE: The original Excel DEC2BIN 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 DEC2BIN function creates "negative" binary numbers with other sources on this topic like here and here. But, on the plus side, this DAX version can handle decimal numbers greater than 511, which is a limitation of Excel's DEC2BIN function.

eyJrIjoiMmNhNDdiZjAtNzk4YS00ZmY1LWE3YjgtZDJhODU3ODJiNTBiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9