Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JulianaMacedo
Helper I
Helper I

Add columns based on Bitwise operation

Hi!

 

I need to separate every bit in a byte and create a column for every bit that will state "Activated" or "the name of the bit" when a bit is activated.

The solution that I'm doing now is partially working and I can't figure out why it is not fully working.


I have created 8 different custom function, one for each bit. It takes the column with one byte as parameter and it performs two bitwise operations, the first shifts to the right the bit that is in the position I want to evaluate, and the second one adds the number 1 to that bit, if the result of this sum is 1 then the bit is activated and I'm calling it "BIT + the position of the bit" (BIT 3 is the bit in the 3rd position from right to left).

 

Rigth, it worked fine with bit 1 and 2, but when I got to bit 3 it is not working correctly. So this is the costum function:

 

Function for bit 1:

 

 

 

(Byte0 as number) => if Number.BitwiseAnd(Number.BitwiseShiftRight(Byte0 as number,1),1) = 1 then "BIT 1" else ""

 

 

 

 

Function for bit 3:

 

 

 

(Byte0 as number) => if Number.BitwiseAnd(Number.BitwiseShiftRight(Byte0 as number,3),1) = 1 then "BIT 3" else ""

 

 

 

 

and this is a sample of the numbers:

Binary numberWhat I see as a result nowWhat do I want to see
10000010BIT 1, BIT3BIT 1
10001000BIT3BIT3

 

In the first example, only BIT 1 and BIT 7 are activated, therefore that is the result I'm expecting, but somehow it's giving me BIT 3 as well as it was activated. Note that I stopped on BIT 3, I did go further using DAX though and got the same errors / mistakes...in some cases it was giving the correct results in order or not.

Anyone has any idea why this is happening?

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @JulianaMacedo 

It's not working because the 10000010 is being interpreted not as a binary number but as a decimal number by PQ. Try shifting 10000010 to the right and see the result. It won't be 1000001    

You can store the 10000010 as text and then access the individual figures with

Text.ToList([BinaryNumber]){0}  for the first figure (from the left)

Text.ToList([BinaryNumber]){1}  for the second figure (from the left)

etc

then check whether that figure is a "1" (note you have to check for the character 1, not  number

1, hence the "").

So for bit 1 you would have

 

(Byte0 as text) => if Text.ToList( Byte0 ){6} = "1" then "BIT 1" else ""

 

for bit 3

 

(Byte0 as text) => if Text.ToList( Byte0 ){4} = "1" then "BIT 3" else ""

 

etc

Note the number for accessing the different positions in the string is inverted vs what you were using

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

2 REPLIES 2
JulianaMacedo
Helper I
Helper I

@AlB  right! that makes sense... thanks a lot, it does seems like this work around solution solved the problem.

AlB
Super User
Super User

Hi @JulianaMacedo 

It's not working because the 10000010 is being interpreted not as a binary number but as a decimal number by PQ. Try shifting 10000010 to the right and see the result. It won't be 1000001    

You can store the 10000010 as text and then access the individual figures with

Text.ToList([BinaryNumber]){0}  for the first figure (from the left)

Text.ToList([BinaryNumber]){1}  for the second figure (from the left)

etc

then check whether that figure is a "1" (note you have to check for the character 1, not  number

1, hence the "").

So for bit 1 you would have

 

(Byte0 as text) => if Text.ToList( Byte0 ){6} = "1" then "BIT 1" else ""

 

for bit 3

 

(Byte0 as text) => if Text.ToList( Byte0 ){4} = "1" then "BIT 3" else ""

 

etc

Note the number for accessing the different positions in the string is inverted vs what you were using

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors