select all rows for an id if 1 row matches condition

[Anonymous]

Hi All,

I am having trouble with a query. I want to identify all rows if 1 row matches a condition. e.g my table looks like below:

ID	Type
1	Home Care
1	Direct Payment
2	Day Care
2	Residential
2	Direct Payment
3	Home Care
3	Day Care
3	Residential
3	Supported Living
4	Direct Payment
4	Day Care

I want to highlight or select or rows where type = Residential therefore the output should look like below:

ID	Type	Yes/No
1	Home Care	No
1	Direct Payment	No
2	Day Care	Yes
2	Residential	Yes
2	Direct Payment	Yes
3	Home Care	Yes
3	Day Care	Yes
3	Residential	Yes
3	Supported Living	Yes
4	Direct Payment	No
4	Day Care	No

So all matching id's will have yes if 1 row matches where type = Residential.

is this possible to do? i have searched in google but cannot find a solution to my query.

kind regards

Hetal

[Anonymous]

Thank you very much. it works as expected.

kind regards

Hetal

[AlB · Accepted solution]

Hi @Anonymous 

NewCol = 
VAR list_ = CALCULATETABLE(DISTINCT(Table1[Type]), ALLEXCEPT(Table1, Table1[ID]))
VAR soughtType_ = "Residential"
RETURN
IF ( soughtType_ IN list_, "Yes", "No" )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

i need to tweak the formula slightly. if the residential has ended before end of the month the it needs to show No. is this posssible?

[AlB]

@Anonymous 

Where are the dates? Please provide as data sample with all the relevant fields and show the expected result for the sample

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

i am getting error message about 'post flooding detected'.

using the same table, ID 2 and where the type is Residential has an end date of 24/4/2021 therefore i need the Yes/No column to show as No.

is this possible?