cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors