Create measure in Power BI to flag record based on condition in other, non-unique table

[zoltansn]

Hi all,

I have a table that is unique on ID, and another that is not. I need to create a measure to flag those IDs that appear in the other table with a condition. Let Friends be

ID	Name
1	Ben
2	Jerry
3	Ruth
4	Lesley

Let FriendFamily be

ID	FamilyMember
1	kid*
1	pet
1	partner
2	kid
2	kid
2	kid
3	pet
4	pet
4	partner

*human not goat

The tables are connected on ID (one to many). I would like a measure that flags each ID in Friends based on whether or not they have a pet. The tables are in a database, Direct Query is my only option (the real tables are massive). So far I've tried DISTINCTCOUNT in FriendFamily, with which I got to a point where I could have a table visual with 3 columns: ID, has_pet, has_human, but then I got lost in trying to figure out how to create calculations with this information. It wouldn't work as a filter for instance, when I tried to apply it to a card that showed distinct count on ID. The business requirements are:

• how many distinct IDs are there with only pet family members?
• how many distinct IDs are there with both pet and human family members?

The measures I've managed to build are below.

Has human family member = CALCULATE(DISTINCTCOUNT(FriendFamily[ID]),FriendFamily[FamilyMember] <> "pet")

Has pet family member = CALCULATE(DISTINCTCOUNT(FriendFamily[ID]),FriendFamily[FamilyMember] = "pet")

How do I get from these (or from scratch) to having flags in Friends like so (I don't need all these, just included various types, see, what's easiest and/or quickest to calculate):

ID	Name	(measure)HasPet	(measure)HasHuman	(measure)HasBoth	(measure)FamilyType
1	Ben	1	1	1	Has Both
2	Jerry	0	1	0	Only Human
3	Ruth	1	0	0	Only Pet
4	Lesley	1	1	1	Has Both

Thank you in advance!

[Ashish_Mathur · Accepted solution]

Hi,

Drag ID from the Friends table and drag this measure to the visual

Family members = if(DISTINCTCOUNT(FriendFamily[FamilyMember])=1,if(MAX(FriendFamily[FamilyMember])="kid","Only human","Only pet"),"Has Both")

Hope this helps.