cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
Super User

Hi,

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

6 REPLIES 6
Anonymous
Not applicable

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

Super User

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.

Anonymous
Not applicable

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.

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.

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

Anonymous
Not applicable

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

Super User

Hi,

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors