Count the number of times a specific value appears in a column when there are more than one value in some of the rows

Question

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 are more than one value in some of the rows?

For example;

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

Result

You could do a `COUNTIF` with the criteria surrounded by wildcards if you want to keep it simple eg. `=COUNTIF(A:A,"*"&C1&"*")` — Kairu, Apr 20 '23 at 03:40

score 2 · Accepted Answer · answered Apr 20 '23 at 04:48

Assuming you've already generated a table (named Values) comprising a single-column (named Value) of distinct values ("AA", "AB", "AC", for example), create this measure:

=
VAR ThisValue =
    MIN ( 'Values'[Value] )
RETURN
    COUNTROWS ( FILTER ( Table1, SEARCH ( ThisValue, Table1[Value],, 0 ) > 0 ) )

and add it to your visual alongside the Value field from the Values table.

score 2 · Answer 2 · answered Apr 20 '23 at 12:27

step1. create column & measure for AA,AB,AC:

COLUMN:

AA = if(search("AA",[Value],,0)>0,1,0)

MEASURE:

AA measure = sum('Value'[AA])

same as AB,AC.

step2. create new table:

Result = datatable(
"Category",STRING,
{{"AA"},{"AB"},{"AC"}})

step3. add column in new table:

Value = switch(true(),
[Category]="AA",[AA measure],
[Category]="AB",[AB measure],
[Category]="AC",[AC measure])

David Leal · Answer 3 · 2023-04-20T04:03:50.800

In case you want to do it using Excel formulas only, you can try the following. Put in cell C1, the following formula:

=LET(A, A2:A8,values,DROP(REDUCE("",A,LAMBDA(ac,x,
 VSTACK(ac,TEXTSPLIT(x,,"; ",1)))),1), ux, UNIQUE(values), cnts, 
 BYROW(ux, LAMBDA(x, SUM(N(values=x)))),
 VSTACK({"Category","Result"},HSTACK(ux, cnts)))

Here is the output:

For more information check my answer to the following question related to the REDUCE/VSTACK pattern: how to transform a table in Excel from vertical to horizontal but with different length.

If you don't have a large dataset that after joining the entire column won't reach the max cell size (32,767 number of characters), then you can try the following:

=LET(A, A2:A8, values,TOCOL(TEXTSPLIT(TEXTJOIN(",",,A),"; ",",",1),2),
 ux, UNIQUE(values), cnts,BYROW(ux, LAMBDA(x, SUM(N(values=x)))),
 VSTACK({"Category","Result"}, HSTACK(ux, cnts)))

The second solution is about 100x faster than the first one.