cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataDownUnder
Frequent Visitor

Countrows where a client works with only one sales team

Hi,  I have what I believe will be a simple one but am struggling.  I have a sales table which has two columns [ClientID] and [Division].  I want to know how many clients have only worked with one division.  A client may work with the same division multiple times (multiple rows) but I only want to know if they have worked with one.

Hope this makes sense.  I have managed to use a filter for a table to show the clients,  but am unable to work out a countrows function to tell me how many there are.

 

Appreciate anay help.

 

 

1 ACCEPTED SOLUTION

Hi,

Thank you for your feedback.

I assume you want a Card Visualization to show a number of clients who worked with only one division.

Please check the below picture and the attached pbix file.

 

Untitled.png

 

Count of clients who worked with only one division measure: =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Client[Client] ),
            "@divisioncount", [Division count measure:]
        ),
        [@divisioncount] = 1
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
DataDownUnder
Frequent Visitor

Thank you for your help, unfortunately still not working.  If I replicate the Dax,  it is assigning every client a score of "1" and therefore the total for "Client working with one division is just the total number of clients.

I have this which logically should work

ClientOneDivision =
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES (Sales[Client ID]),
"@divisioncount", COUNTROWS(( SUMMARIZE(SALES,DIM_Clients[Client ID],DIM_Division[Division])))
),
[@divisioncount] = 1
)
)

While the measure used "COUNTROWS(( SUMMARIZE(SALES,DIM_Clients[Client ID],DIM_Division[Division])))" by itselg in a table with "Client name" will produce various scores based on how many divisions they work for, as soon as I place it in the above measure to get the toal number of =1 ,  it goes blank in a score card.
Not sure I'm going to solve this one and I thought ti so straight forward....


 

Hi,

Sorry that it is difficult for me to understand what is your desired outcome.

Do you want to create a calculated measure? Is it card visualization? Is it table visualization? Do you want to create a calculated column in which table? 

It will be very much appreciated if you can share how your expected outcome with correct number looks like.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you,  appreciate the help.  I just went back and started again with the solutions you suggested and they worked!  I must have been doing somethign funny with the syntax when copying.  Possibly I was using the fact table rather than related tables as per the query.

Thank you so much, really appreciate the effort and help on this JiHwan Kim.

 





 



Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but without seeing your data model, it is difficult for me to provide the most accurate solution.

However, please check the below picture and the attached pbix file.

I tried to create a sample pbix file like the attached file, and based on my sample, the below is for creating a measure to find out how many divisions are involved per client.

 

I hope the below can provide you ideas on how to create the solution that fits your data model.

 

Untitled.png

 

Division count measure: =
COUNTROWS ( SUMMARIZE ( RELATEDTABLE ( Data ), Division[Division] ) )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you,  I have those numbers already which is good,  I just need to count the number of clients with the value = 1

I tried this but not getting the single count I am after


PracticeClientDIB =
VAR Logical = COUNTROWS(SUMMARIZE(Sales[Client ID],Sales(Division]))
RETURN
IF(Logical = 1, COUNTROWS(Sales))

 

Hi,

Thank you for your feedback.

I assume you want a Card Visualization to show a number of clients who worked with only one division.

Please check the below picture and the attached pbix file.

 

Untitled.png

 

Count of clients who worked with only one division measure: =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Client[Client] ),
            "@divisioncount", [Division count measure:]
        ),
        [@divisioncount] = 1
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors