Counting customers that used multiple channels

[Anonymous]

Hi all, I am trying to count the number of customer names who used both email AND mobile based on these two tables. Given the example data, I want to be able to show in a report that the number of customer names who used both email AND mobile was 1 (only customer A). Customer B did not use both. How would I do this?

I also would like to calculate the average number of Channels used (channel total > 0) for a Customer. Any ideas?

Customer Table

Customer	Product ID
A	A1
A	A2
B	B1
B	B2

Feature Usage

Product ID	Channel	Channel Total
A1	Email	10
A2	Mobile	20
B1	Email	50
B2	Mobile	0

Sample output: 2 KPI cards

KPI Card 1: # of Customers who only used Email Channel (i.e. Customer B)

1

KPI Card 2 : # of Customers who used both Email and Mobile (i.e. Customer A)

1 

[amitchandak]

@Anonymous , I doubt the data model is correct. You should have a customer table, product table, and customer product and feature tables.

If these two tables are joined on the product , this one should able to give you an answer

sumx(summarize(Feature,Customer [Customer],Customer[Product ID],Feature[Channel], "_1", max(Feature[Channel Total])),[_1])

[dax]

Hi @Anonymous 

As @parry2k  mentioned, your current data is based on relationship of table, and when the relationship are many to many or many to one, the result will be different. So if possible, could you please inform me more sample. You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@dax I have a many to many relationship between the two tables, which are related by Product ID. How can I modify the measures to account for this?

[dax]

Hi @Anonymous , 

If possible, could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax , I have updated my post. It contains both the same data (2 tables), and expected output (2 KPI cards). Please let me know if you can assist. Thanks

[parry2k]

@Anonymous how come the customer table has duplicate customer records? How your data model looks like? The solution will heavily depend on the data model and the relationship between the tables

[Anonymous]

@parry2k @amitchandak @dax there is a many to many relationship between two tables, and they are related by Product ID. I know it isn't an ideal data model, but it is what I have available to me.