Assign accounts to Household

[Anonymous]

Let's say I have the following data:

- 10 Total Account numbers (row records), have one product type of Phone or Internet

- 5 Unique Households (say they're called "Household 1"..."Household 5")

- Each Household (HH) has 2 Accounts

- A HH can be classified as "Double Phone" = 2x Phones,  "Double Internet" = 2x Internet, or "Mix" = 1x of each 

So, how do I assign that HH classification to each account?

And ideally, that new field (assignment) could be used for grouping purposes and I could study that group.

[Anonymous · Accepted solution]

HH = 
VAR varPhone = CALCULATE(COUNTROWS('Data'),FILTER(ALLEXCEPT('Data','Data'[Household]),'Data'[Product] = "Phone"))
VAR varInternet = CALCULATE(COUNTROWS('Data'),FILTER(ALLEXCEPT('Data','Data'[Household]),'Data'[Product] = "Internet"))
RETURN
    IF( varPhone > 0, IF(varInternet >0, "Mix", "Phone"),"Internet")

You can try the above. And add this code as a calculated column to your table.  It will give you the following result.