Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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
)
)
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.
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
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.
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.
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.
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
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
)
)
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |