topic Re: Count items in COLUMN 1, filtered by COLUMN 2, grouped by COLUMN 3 ? in DAX Commands and Tips

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

Anonymous — Mon, 27 Jan 2020 12:31:15 GMT

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.

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

ibarrau — Mon, 27 Jan 2020 12:55:19 GMT

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.

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

Anonymous — Mon, 27 Jan 2020 14:25:38 GMT

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?

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

ibarrau — Mon, 27 Jan 2020 14:32:02 GMT

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,