Count how many times a value appears in a column when there are more than one value in the rows

[bammyd36]

Hello Power bi Community,

Please is there a measure or DAX in power bi that can be used to count the number of times a specific value appears in a column when there is more than one value in some of the rows?

For example;

TABLE XX

Date	Value
01/08/2017	AA
02/08/2017	AA
03/08/2017	AB
04/08/2017	AA; AB
05/08/2017	AC
06/08/2017	AB
07/08/2017	AA; AC

I tried the measure below but I didn't get the desired result

CountValues = CALCULATE ( COUNTROWS ( TableName ); TableName[ColumnName] = “This Value” )

I don't want to unpivot the table because it will mess with the other data.

Result

Category	Result
AA	4
AB	3
AC	2

Thank you.

[tamerj1]

Hi @bammyd36 

please try

Result =
SUMX (
VALUES ( Category[Category] ),
COUNTROWS (
FILTER ( 'Table', CONTAINSSTRING ( 'Table'[Value], Category[Category] ) )
)
)

[bammyd36]

Thank you for your reply.

I want to count the number of times each item in the Value column appears in the Table.

i.e AA appears 4 times, AB appears 3 times and AC appears 2 times.

[tamerj1 · Accepted solution]

Hi @bammyd36 
Please refer to the attached sample file containing the same proposed solution but explained in more details.
Initially I assumed that you already have the category table. However, you can create it using the following DAX

Category = 
DISTINCT (
    SELECTCOLUMNS (
        GENERATE ( 
            VALUES ( 'Table'[Value] ),
            VAR String = 'Table'[Value]
            VAR Items = SUBSTITUTE ( String, "; ", "|" )
            VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
            VAR T = GENERATESERIES ( 1, Length, 1 )
            RETURN
                SELECTCOLUMNS ( T, "@Item", PATHITEM ( Items, [Value] ) )
        ),
        "Category", [@Item]
    )
)

Then to obtain the desired result use the same measure proposed in my original reply

Result = 
SUMX (
    VALUES ( Category[Category] ),
    COUNTROWS (
        FILTER ( 'Table', CONTAINSSTRING ( 'Table'[Value], Category[Category] ) )
    )
)

[youssefabdelwah]

When I use the same DAX calculation to make a seperate table and not a temporary visual , i get a sum and not the count . Do you know why perhaps?

[tamerj1]

Hi @youssefabdelwah 

Replace SUMX - VALUES with COUNTROWS