Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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"
Solved! Go to 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!!
Hi @IoannisT ,
You can produce your required output in the following manner by tweaking your formula.
I attach a pbix file as an example.
Hi @IoannisT ,
You can produce your required output in the following manner by tweaking your formula.
I attach a pbix file as an example.
Thank you very much. Wordek like a charm
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!!
Thank yo uso much. It works!
User | Count |
---|---|
120 | |
66 | |
66 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
68 | |
65 | |
54 |