Defining name for every 0 or 1 of the binary data

[Anonymous]

I have a data containing Binary data of 38 character (self chosen length).

Each of the data is describing a sensor which is either on = 1 or off = 0.

I would love to define each of those sensors and give it a name (in this case a numbername).

However, with my current method using MID and IF it only finds and accepts the first on = 1 state occurance, while ignoring the rest of 1s in the same row.

NB! there musn't be anything like 37, 33. just 1number  according to the list at each occurance of 1 binary.

see below picture:

[v-yuta-msft]

@Anonymous ,

I have made a simple sample data like below:

 

MID(URLLeast[Dec2Bin_func], 1, 1) will return "0"(text format) which is recognized as 0/FALSE, so the first IF condition will be skipped.

MID(URLLeast[Dec2Bin_func], 1, 2) will return "01"(text format) which is not equal to 0, so this condition is recognized as TRUE, so the value 37 in second IF condition will be returned.

Once a condition has been returned, the extra conditions will be skipped.

So if you modify the first condition like below:

TLstatus = IF(MID(URLLeast[Dec2Bin_func], 1, 1) = "0", 38, IF(MID(URLLeast[Dec2Bin_func], 1, 2), 37, IF(MID(URLLeast[Dec2Bin_func], 1, 3), 36)))

The result will be changed:

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuta-msft 

Unfortunately, that is not the solution I was asking. Maybe I should have explained it better.

What I want to know is:

Wether it is possible to give name to each one of the binary in a row, as such (example):
Binary:  00100000000000010000000000001000000000

Name to be given (left to right): 38, 37 .....................................3,2,1

For each occurance of 1 in the binary above one of the number(name), which is defined according to binary position shown. so for the binary above it would be: 36  22 10 must be shown.
However, with the code I have generated I can only see the first occurance of "1" shown as number i.e. 36, however I dont see 22 or 10. I know it is because 1 row can only give 1 feedback of result.

So a solution would be to make dax to create a copy of the row and then count the second "1" so it now shows the same row but also 22 and thus a new row for 10 etc... So it creates a new copy row for each time 1 occurs and then assign the name for it..

Any idea of how to do this?

[v-yuta-msft · Accepted solution]

@Anonymous ,

I'm afraid DAX can't achieve your requirement because DAX engine doesn't support loop/iteration. You can use python script to achieve this. In query editor, click python script and implement code below:

# 'dataset' holds the input data for this script

import pandas as pd

Tool = [38, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21, 20,
        19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]

Result = []

for index, row in dataset.iterrows():
    Origin_List = list(row["Data"])
    for i in range(len(Origin_List)):
        if Origin_List[i] == "1":
            Result.append(str(Tool[int(i)]) + " ")
    dataset["Result"] = str(Result)

The result will be like this:

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.