Create a new column based on specific combination of column values.

[Walid_Abou]

I want to create a new column that classifies IDs based on whether they are seen in combination with another specific value.

I have tried with IF, IF contains and look-up functions but with no luck. I just get a replication or count of 'SomeValue'. What I want is to set 'Y' or '1' in the new column whenever an ID has 'X' in SomeValue no matter the frequency. 

The HasX should be the desired outcome as shown below

ID	SomeValue	HasX?
1		Y
1	X	Y
1		Y
2		Y
2	X	Y
3		N
4		N
4		N

[FreemanZ]

hi @Walid_Abou 

try to add a column like:

Column = 
VAR _list = 
CALCULATETABLE(
    VALUES(data[SomeValue]),
    data[ID]=EARLIER(data[ID])
)
RETURN
IF(
    "X" IN _list,
    "Y", "N"
)

it worked like:

[DOLEARY85 · Accepted solution]

Hi,

try this:

Column = if(CALCULATE(CONTAINS('Table (5)','Table (5)'[SomeValue],"X"),ALLEXCEPT('Table (5)','Table (5)'[ID]))=TRUE,1,0)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍