Forum Discussion
cottrera
Post Prodigy
Post ProdigyCount unique values if > 1
Hi
I have a table that contains Property ref, Trad, Supplier and unique. The unique is a concatenation of Property-Trade-SupplierI have a report that filters by Suppplier. I am tryng to count the number of times the unique value appears if the count is greater that 1. If the count equals to 1 I am not interested.
Table example
| Property | Trade | Supplier | Unique |
| 1 | ELEC | ACME | 1-ELEC-ACME |
| 1 | ELEC | ACME | 1-ELEC-ACME |
| 1 | PLUM | ACME | 1-PLUM-ACME |
| 2 | PLUM | ACME | 2-PLUM-ACME |
| 3 | CARP | ACME | 3-CARP-ACME |
| 4 | CARP | ACME | 4-CARP-ACME |
| 4 | CARP | ACME | 4-CARP-ACME |
| 4 | CARP | ACME | 4-CARP-ACME |
| 4 | CARP | ACME | 4-CARP-ACME |
| 4 | CARP | ACME | 4-CARP-ACME |
| 5 | PLUM | ACME | 5-PLUM-ACME |
| 5 | PLUM | ACME | 5-PLUM-ACME |
| 6 | ELEC | ABC | 6-ELEC-ABC |
| 7 | ELEC | ACME | 7-ELEC-ACME |
| 7 | ELEC | ACME | 7-ELEC-ACME |
| 7 | PLUM | ABC | 7-PLUM-ABC |
| 8 | PLUM | ABC | 8-PLUM-ABC |
This sumarry of the above table shows you that the supplier called ACME had 4 incidences where the count of the unique column was > 1
| Supplier & unique | Count of Unique |
| ABC | 3 |
| 6-ELEC-ABC | 1 |
| 7-PLUM-ABC | 1 |
| 8-PLUM-ABC | 1 |
| ACME | 14 |
| 1-ELEC-ACME | 2 |
| 1-PLUM-ACME | 1 |
| 2-PLUM-ACME | 1 |
| 3-CARP-ACME | 1 |
| 4-CARP-ACME | 5 |
| 5-PLUM-ACME | 2 |
| 7-ELEC-ACME | 2 |
Thank you Richard
Hi,
Please check the below picture and the attached pbix file.
Countrows meausre greater than one: = COUNTROWS ( SUMMARIZE ( FILTER ( Data, [Countrows measure:] > 1 ), Data[Unique] ) )
2 Replies
cottreraGreat thank you for your quick resonse 😀
- Jihwan_Kim
Super User
Hi,
Please check the below picture and the attached pbix file.
Countrows meausre greater than one: = COUNTROWS ( SUMMARIZE ( FILTER ( Data, [Countrows measure:] > 1 ), Data[Unique] ) )