cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

Distinct Count of values based on conditions

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"?

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

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])

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors