Create a column based on multiple values

[noah44]

Hi,

I am building a report about power consumption per customer.

There are two types of values that I can get, let's say they are "w" and "s"

Now I want to create a column that contains "0" when an ID has only one type of values; and "1" when an ID has both types.

It could look like this. ID 1 has only "w" values so Cust. type is "0". ID 2 has both types so Cust. type is "1".

It is important that all rows of an ID get the same Cust. type even if there is only one different Value type.

Thanks 

Noah

[amitchandak · Accepted solution]

@noah44 , A new column

new column =
var _1 = countx(filter(Table, [ID] = earlier([ID]) && [Value Type] ="w"),[ID])
var _2 = countx(filter(Table, [ID] = earlier([ID]) && [Value Type] ="s"),[ID])
return
if(not(isblank(_1)) && not(Isblank(_2)), 1,0)