Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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
Hi @Anonymous,
Yes, because in the distinct count considerer each product one time.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |