Forum Discussion
PowerBI: Count values in a Matrix
Hello All,
I am trying to figure out how to count the values inside a matrix in a measure.
My raw data looks like this. I have a large number of values and a large number of categories that the Category Type can be.
| VALUE# | Category Type |
| Value1 | Category 1 |
| Value1 | Category2 |
| Value2 | Category3 |
| Value 2 | Category 3 |
| Value 3 | Category 30 |
I would like to be able to identify if any duplicates exist per Value.
For example, in a matrix it would look like:
| Category1 | Category2 | CategoryN | |
| Value1 | Count of category1 | Count of category2 | Count of categoryN |
| Value2 | Count of category1 | Count of category2 | Count of categoryN |
| Value3 | Count of category1 | Count of category2 | Count of categoryN |
So that my final data can look like:
| Is there any duplicate? | |
| Value 1 | No |
| Value2 | Yes |
| Value3 | No |
I am interested in wrapping this up to a measure rather than show the visualization of the matrix in the report.
Note your example data has spaces in it that result in no duplicates. Once those are removed in query, this measure expression works in a table visual with the Value# column to get your desired result.
Duplicates =
VAR summary =
SUMMARIZE (
CatTypes,
CatTypes[Category Type],
"@count", COUNT ( CatTypes[Category Type] )
)
RETURN
IF (
COUNTROWS (
FILTER (
summary,
[@count] > 1
)
) > 0,
"Yes",
"No"
)Regards,
Pat
5 Replies
- AlB
Community Champion
Hi eryan123
Measures only return scalars, not tables. You can create a calculated table:
NewTable = ADDCOLUMNS ( DISTINCT ( Table1[Value] ), "IsThereDuplicate", VAR dupsFound_ = CALCULATE ( DISTINCTCOUNT ( Table1[Value] ) ) > CALCULATE ( DISTINCTCOUNT ( Table1[Value] ) ) RETURN IF ( dupsFound__, "Yes", "No" ) )I would recommend a visual with a measure though
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- mahoneypat
Microsoft Employee
Note your example data has spaces in it that result in no duplicates. Once those are removed in query, this measure expression works in a table visual with the Value# column to get your desired result.
Duplicates =
VAR summary =
SUMMARIZE (
CatTypes,
CatTypes[Category Type],
"@count", COUNT ( CatTypes[Category Type] )
)
RETURN
IF (
COUNTROWS (
FILTER (
summary,
[@count] > 1
)
) > 0,
"Yes",
"No"
)Regards,
Pat
mahoneypat
Thank you for your reply.
I am actually interested in the duplicates only. I would like to be able to identify those because they mean 2 failures have occurred.
If only one failure occures(does NOT have a duplicate), I would like to exclude that.- mahoneypat
Just change the "No" part of the IF to BLANK(). Once you do that rows that do not have duplicates will automatically filtered out of the visual.
Regards,
Pat
