Forum Discussion
Advocate ICount specific words separate by comma on row and exclude empty cells
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"
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!!
Hi IoannisT ,
You can produce your required output in the following manner by tweaking your formula.
I attach a pbix file as an example.
4 Replies
- Kishore_KVN
Solution Sage
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!!
IoannisTThank yo uso much. It works!
- DataNinja777
Super User
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