Count specific words separate by comma on row and exclude empty cells

[IoannisT]

Hi Fabric community.

I have had a search around and found partially the solution but I am having trouble excluding empty cells on the following count.

They say 1 photo is 1000 words so here is the problem with attached DB 

- I am using the following measure but null values are counted as "1!

DoA_Individual_Count = 
SUMX(
    'test',
    LEN('test'[Approved_Actions])
        -LEN(
            SUBSTITUTE(
                'test'[Approved_Actions],
                ",",
                ""
             )
        )+1)

Any ideas?

User_ID	Approved_Actions	Count_of_approved_Actions
1	a	1
2	a,b	2
3	a,c	2
4	b,c,d	3
5	d	1
6	a	1
7		"0" or "null"
8	d	1
9		"0" or "null"
10		"0" or "null"

[Kishore_KVN · Accepted solution]

Hello @IoannisT ,

Please change your measure as below:

DoA_Individual_Count = 
IF(MAXX('Table','Table'[Approved_Actions])<>BLANK(),
SUMX(
    'Table',
    LEN('Table'[Approved_Actions])
        -LEN(
            SUBSTITUTE(
                'Table'[Approved_Actions],
                ",",
                ""
             )
        )+1),0)

Output looks as below:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[DataNinja777 · Accepted solution]

Hi @IoannisT ,

You can produce your required output in the following manner by tweaking your formula.  

I attach a pbix file as an example.  

[IoannisT]

Thank you very much. Wordek like a charm

[Kishore_KVN · Accepted solution]

Hello @IoannisT ,

Please change your measure as below:

DoA_Individual_Count = 
IF(MAXX('Table','Table'[Approved_Actions])<>BLANK(),
SUMX(
    'Table',
    LEN('Table'[Approved_Actions])
        -LEN(
            SUBSTITUTE(
                'Table'[Approved_Actions],
                ",",
                ""
             )
        )+1),0)

Output looks as below:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[IoannisT]

Thank yo uso much. It works!