Forum Discussion

eryan123's avatar
eryan123
Frequent Visitor
5 years ago
Solved

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 ...
Power
Power BI Dax
Power KPI Matrix
Power Pivot
PowerBI Community
PowerBI Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
eryan123
Frequent Visitor
5 years ago

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?