Countrows where a client works with only one sales team

[Anonymous]

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.

[Anonymous]

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....

[Jihwan_Kim]

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.

[Anonymous]

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]

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.

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

[Anonymous]

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

[Jihwan_Kim · 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.

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