How to count the number of times a value appears in a table and then sum it

[Rob_Morris]

I have a number of columns in a table as below

UPRN	Outcome
789	Justified
123	Justified
456	Justified
123	Justified
123	Not justified
456	Not justified
123	Justified

What I would like to do is count the number of times a UPRN appears in the table, where the outcome code is "Justified".

So in the table above what I would end up with is an additional column / measure like below:

UPRN	Outcome	Count of justified for UPRN
789	Justified	1
123	Justified	3
456	Justified	1
123	Justified	3
123	Not justified	3
456	Not justified	1
123	Justified	3

I would then need something to tell me how many unique UPRN's are recorded against the Count of Justified for UPRN column ultimately ending up with the table below:

Count of justified for UPRN	Unique UPRN's that match
1	2
2	0
3	1
4	0

[v-stephen-msft · Accepted solution]

Hi @Rob_Morris ,

The pbix file is here.

Best Regards,

Stephen Tao

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

[Rob_Morris]

Thank you for this.

Very helpful and did exactly what I wanted.

[Anonymous]

@Rob_Morris  you can use the below measure for count of justified & Non justified

countof j = calculate(countrows('Table'),'Table'[Outcome]="Justified" || 'Table'[Outcome]="Not Justified")

distnctof j = calculate(countrows(values('Table'[UPRN])),'Table'[Outcome]="Justified" || 'Table'[Outcome]="Not Justified")

Appreciate your Kudos, Press the thumbs up button!!👍

Regards,

Husna