Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Responsive Resident

## Customers have different services .Find the number of Customers with more than one service

I have a table as follows.

 Client_ID ClientName ClientProduct Site BilledAmount 1 Client A Product A Site A 2300 1 Client A Product A Site B 2550 1 Client A Product B Site A 2340 2 Client B Product A Site A 2700 2 Client B Product C Site A 3400 3 Client C Product B SiteA 4500 4 Client D Product D Site A 3600

Different clients have products. These products could be on different or the same sites of the client. I would like to know.

1. How many clients have more than 1 ClientProduct?

2. How many clients have more than one 1 clientProduct in the same site ?

Just submit your answer if you can tackle any of them..
Thanks.

2 ACCEPTED SOLUTIONS
Super User

Hi @Datagulf
For first request try the measure :

DistinctClientCount =
CALCULATE(
DISTINCTCOUNT('table'[Client_ID]),
FILTER(
'table',
CALCULATE(
DISTINCTCOUNT('table'[ClientProduct]),
ALLEXCEPT('table', 'table'[Client_ID])
) > 1
)
)

For the second :

DistinctClientCountSameSite =
CALCULATE(
DISTINCTCOUNT('table'[Client_ID]),
FILTER(
'table',
CALCULATE(
DISTINCTCOUNT('table'[ClientProduct]),
ALLEXCEPT('table', 'table'[Client_ID], 'table'[Site])
) > 1
)
)

Link to a sample file

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

Super User

Hey @Datagulf ,

you can create two calculated columns like so:

``````client has more than product =
var currentClientID = 'Table (2)'[Client_ID]
var noOfPrudcts =
CALCULATE(
DISTINCTCOUNT( 'Table (2)'[ClientProduct] )
, ALL( 'Table (2)' )
, 'Table (2)'[Client_ID] = currentClientID
)
return
IF( noOfPrudcts > 1 , "more than one product" , "only one product" )``````

and so:

``````spread across sites =
var currentClientID = 'Table (2)'[Client_ID]
var noOfSites =
CALCULATE(
DISTINCTCOUNT( 'Table (2)'[Site] )
, ALL( 'Table (2)' )
, 'Table (2)'[Client_ID] = currentClientID
)
return
IF( noOfSites > 1 , "more than one site" , "only one site" )``````

The table then will look like this:

I decided not to create a measure because I assume it's helpful if you can use these columns inside slicers.

Hopefully, this provides what you are looking for.

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
3 REPLIES 3
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

Hey @Datagulf ,

you can create two calculated columns like so:

``````client has more than product =
var currentClientID = 'Table (2)'[Client_ID]
var noOfPrudcts =
CALCULATE(
DISTINCTCOUNT( 'Table (2)'[ClientProduct] )
, ALL( 'Table (2)' )
, 'Table (2)'[Client_ID] = currentClientID
)
return
IF( noOfPrudcts > 1 , "more than one product" , "only one product" )``````

and so:

``````spread across sites =
var currentClientID = 'Table (2)'[Client_ID]
var noOfSites =
CALCULATE(
DISTINCTCOUNT( 'Table (2)'[Site] )
, ALL( 'Table (2)' )
, 'Table (2)'[Client_ID] = currentClientID
)
return
IF( noOfSites > 1 , "more than one site" , "only one site" )``````

The table then will look like this:

I decided not to create a measure because I assume it's helpful if you can use these columns inside slicers.

Hopefully, this provides what you are looking for.

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User

Hi @Datagulf
For first request try the measure :

DistinctClientCount =
CALCULATE(
DISTINCTCOUNT('table'[Client_ID]),
FILTER(
'table',
CALCULATE(
DISTINCTCOUNT('table'[ClientProduct]),
ALLEXCEPT('table', 'table'[Client_ID])
) > 1
)
)

For the second :

DistinctClientCountSameSite =
CALCULATE(
DISTINCTCOUNT('table'[Client_ID]),
FILTER(
'table',
CALCULATE(
DISTINCTCOUNT('table'[ClientProduct]),
ALLEXCEPT('table', 'table'[Client_ID], 'table'[Site])
) > 1
)
)

Link to a sample file

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

## Helpful resources

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors