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!
