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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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 🙂

1 ACCEPTED SOLUTION
Solution Sage

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 😀

2 REPLIES 2
Solution Sage

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 😀

Frequent Visitor

Very nice!
Thank you very much 🙂

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors