Insert value in group of data depending on value from other column

[Limerick]

Hello together, 

I'm struggeling with a new little Problem.
I have this Table with a lot more rows, but these two rows are the most important:

ID	State
1	Error
1	Error
1	OK
2	Error
2	Error
2	OK
3	Error
3	Error

As you can see, the ID can get two different states. I now need to create a third row, which says that if the ID has once the state OK, then it is OK for every state:

ID	State	Code
1	Error	OK
1	Error	OK
1	OK	OK
2	Error	OK
2	Error	OK
2	OK	OK
3	Error	Error
3	Error	Error

It doesnt matter if it is in DAX or in the Query. 

Thank you in advance 🙂 

[jaideepnema · Accepted solution]

Hi @Limerick ,

Create a calculated column like this:

Code =

var statecheck=CALCULATE(CONCATENATEX('Table','Table'[State],","),ALLEXCEPT('Table','Table'[ID]))

var findvalue=CONTAINSSTRING(statecheck,"OK")

return IF(findvalue,"OK",'Table'[State])

which will give you the desired result:

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[Limerick]

Very nice!
Thank you very much 🙂