PowerBI: Count values in a Matrix

[eryan123]

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. 

[mahoneypat · Accepted solution]

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

[eryan123]

@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

[AlB]

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 

[eryan123]

@AlB 

Thank you for your reply. 

Your calculated table is not referencing the categories. As of now, all the Values are showing that they have duplicates. 
The Values and Duplicates are also not the only columns in the table that I am referencing. 

Any recommendations?