Distinct Count of values based on conditions

[abhishek_2593]

Hi,

I have a dataset where the products are listed in various websites as per the below table.

How can I calculate the distinct count of products based on the websites that they are listed against?

Desired solution like this:
1. Products in all 3 website = Distinct count of Product when website = "Own+Amazon+Other"?

1. Products in any 2 website = Distinct count of Product when website = "any two website"?

Thanks in advance!

[v-jingzhang · Accepted solution]

Hi @abhishek_2593 

You can first create a new table by summarizing the original table with below code. 

Table 2 = SUMMARIZE('Table','Table'[Product],"websites count",DISTINCTCOUNT('Table'[Websites]))

Then create below measures to count from the new table. 

Products in all 3 website = CALCULATE(DISTINCTCOUNT('Table 2'[Product]),'Table 2'[websites count]=3)

Products in any 2 website = CALCULATE(DISTINCTCOUNT('Table 2'[Product]),'Table 2'[websites count]=2)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@abhishek_2593 ,

a new column if that can help

= Calculate(distinctcount(Table[Websites]), filter(Table, [Product] =earlier([Product]) ))

Measure , count of product having 3 websites

Countx(Filter(Summarize(Table, [Product], "_1", distinctcount(Table[Websites])),[_1] =3), [Product])

2

Countx(Filter(Summarize(Table, [Product], "_1", distinctcount(Table[Websites])),[_1] =2), [Product])