link unique identifiers to multiple rows

[Anonymous]

Hi,

I have a table that includes a unique identifier in the form of an idividual ID.  ach row contains this ID and also includes the type of product that individual has (Home or Motor insurance).  I am trying to create a flag, or something of this ilk, that identifies whether an individual has both a home and motor product.

For example, the below shows an idividual has both a home and motor product (across 2 different rows).  Ideally, I like a flag that identifies whetehr this customer is a dual product holder.

Many thanks in advance!

Individual ID	Product
123456	Home
123456	Motor

[MFelix]

Hi @Anonymous,

You can do this adding the Individual and Product in your visual and then do a distinct count, if you need to have it in a more tangible way you can for instace make this measure and add it to your table.

Customer type = IF(
					DISTINCTCOUNT(Table1[Product])>1;
					"Dual Product Holder";
					"Single Product Holder"
				  ) 

Regards,

MFelix

[Anonymous]

Thanks for this @MFelix.

How can I do this but ensure that I don't class a customer who may have 2 home 2 motor products as dual product?  I'm trying to identify those that have both fo the alternative products.  My apoligies, I should have made that clearer in my original post.

regards.

[MFelix]

Hi @Anonymous,

This ensure exactly that because we are doing distinct count, as you can see in the print below I have added a new row with Motor 2 times to customer 123456 in the distinct count and measure the customer only apears as dual although in the count it as 3 products (2 Motors + 1 Home)

Regards,

MFelix

[Anonymous]

Thanks @MFelix

Will this then work and show an individual as a single product holder if they, for example, have 2 motor products and 0 home products?  I only want to show as dual product holder if they have at least 1 of each product.

Thanks

[MFelix]

Hi @Anonymous,

Yes, because in the distinct count considerer each product one time.

Regards,

MFelix

[Anonymous]

Thanks again @MFelix

My last question is where does the individual ID come into it.  The rows in my table contain much more information, other than what's above, and I want to be able to ensure that the distinct count is using the individual ID.

Many thanks.

[MFelix]

The distinct count is of the product and not the Individual ID depending on the column you select Individual ID or other the result is always the same because you are distinct counting the Product ID.

MFelix