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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Datagulf
Responsive Resident
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 Site
A
 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
Ritaf1983
Super User
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
    )
)
Ritaf1983_0-1685765834356.png


For the second :

DistinctClientCountSameSite =
CALCULATE(
    DISTINCTCOUNT('table'[Client_ID]),
    FILTER(
        'table',
        CALCULATE(
            DISTINCTCOUNT('table'[ClientProduct]),
            ALLEXCEPT('table', 'table'[Client_ID], 'table'[Site])
        ) > 1
    )
)
Ritaf1983_1-1685765953513.png

 

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

TomMartens
Super User
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:
image.png

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

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TomMartens
Super User
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:
image.png

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
Ritaf1983
Super User
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
    )
)
Ritaf1983_0-1685765834356.png


For the second :

DistinctClientCountSameSite =
CALCULATE(
    DISTINCTCOUNT('table'[Client_ID]),
    FILTER(
        'table',
        CALCULATE(
            DISTINCTCOUNT('table'[ClientProduct]),
            ALLEXCEPT('table', 'table'[Client_ID], 'table'[Site])
        ) > 1
    )
)
Ritaf1983_1-1685765953513.png

 

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.