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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Count items in COLUMN 1, filtered by COLUMN 2, grouped by COLUMN 3 ?

Good day,

 

I’m brand new to Power BI and working on some reports. I’m wondering if someone can advise how to best work out (I assume via a measure) how many unique ID’s exist for a particular DisplayName, filtered on ClassName. In basic terms, using this example table, I want to know how many IDs exist for each specific Organisation. Or in other words, how do I count items in the FIRST COLUMN, filtered on THE SECOND COLUMN and grouped by the THIRD COLUMN?

 

The results – based on this example – should be:

 

Company 1 = 4

Company 2 = 2

Company 3 = 2

 

I am certain it is not too difficult, and I could do this calculation using SQL, but I’m just trying to wrap my head around Power BI and how calculations work there.

Untitled.png

3 REPLIES 3
ibarrau
Super User
Super User

Hi there. You can just add the column you want to count values in a table visualization and the value to count as a summarization option with downarrow as "distinct count". You can also create a measure like DISTINCTCOUNT(Table[Column]). Then create a visualization with the measure and the category you want to count.

 

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Anonymous
Not applicable

Hi.

 

Are you able to example this based on the data I showed at all? I created a new table visualisation with DisplayName as the first column, filtered that on ClassName = Organisation; which seems like the starting point. I now want to generate a new column with the ID count for each particular organisation but the counts and measures I have tried - including your suggested DISTINCTCOUNT(Table[Column]) - only ever returns the total count of organisations on every row rather than a count of IDs for each Organisation. I should maybe highlight that the data lies across more than 1 table (ID is on one table, ClassName and DisplayName are on another table).

 

Are you (or anyone) able to give me a specific example for this case at all?

 

 

For sure you should share the idea of the data model in order to help us understand your problem. My suggestion was adding a table with organization name and a distinct count of the ID you are looking for DISTINTCOUNT(Table[Id])

If you don't have sensitive data you can share a picture of the relationship view. Otherwise just draw an example in any tool.

 

Regards,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors