New user issue - Count of counts

[rtassicker]

Hi There,

I am new to using PowerBI, I am using an organisational data set in tandem with self-directed learning. Our data set is the output of monthly data quality exercises, so each record shows an ID number, a data input that has been checked, and a column that contains the text ERROR if there was a an error in that data element. Each ID number has around 50 data inputs to check that may or may not show ERROR. 

It is straightforward enough to show a count of ERRORs for a given ID number:

What I would like to do is to be able to count the ID numbers which have 0 errors, 1 error, 2 errors, 3 errors and so on, in order to display in visualisations whether all the errors are concentrated in a small number of records, or spread out with a few here and there. I have tried a few different approaches but I am not yet experienced enough to know the right way to go about it.

Any help will be greatly appreciated!

Thanks,

Russell

[Ashish_Mathur]

Hi,

Refer to this article - https://www.daxpatterns.com/dynamic-segmentation/.  Since this solution uses only measures, the result would respond to slicer selections.

Hope this helps.

[rtassicker]

Thanks Ashish, that's a little beyond me at the moment but it looks very useful 🙂

[Tara_]

Hi @rtassicker ,

You can create a measure that counts the Grant Numbers and use it in the visuals alongside the errors. I created a sample table as below. Hope this helps.

Measure:

GrantCounts = COUNT(Grants[Grant Number])

Visuals:

[rtassicker]

Thanks for the help Tara!

[rajulshah · Accepted solution]

Hello @rtassicker,

I have created a table with 2 columns - Grant Number and Errors.
Then I created calculated columns as below:

IsError = IF(Errors[Errors]<>"",1)

NumberofErrors = CALCULATE(COUNT(Errors[IsError]),FILTER(ALL(Errors),Errors[Grant Number]=EARLIER(Errors[Grant Number])))

Then I plot these columns in table visual as follows:
Here, please select "Don't summarize" for NumberofErrors and "Count" for Grant Number.

Hope this helps

[rtassicker]

Thanks rajulshah, that's helped me find the solution 🙂