Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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