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

Reply
Anonymous
Not applicable

select all rows for an id if 1 row matches condition

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:

 

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

 

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

 

IDTypeYes/No
1Home CareNo
1Direct PaymentNo
2Day CareYes
2ResidentialYes
2Direct PaymentYes
3Home CareYes
3Day CareYes
3ResidentialYes
3Supported LivingYes
4Direct PaymentNo
4Day CareNo

 

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

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Anonymous 

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

 

SU18_powerbi_badge

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.

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Thank you very much. it works as expected.

 

kind regards

Hetal

AlB
Super User
Super User

Hi @Anonymous 

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

 

SU18_powerbi_badge

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
Not applicable

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?

@Anonymous 

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

 

SU18_powerbi_badge

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
Not applicable

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors