Count of blanks showing up as 1 but want 0

[lopa30]

Hello,

I am new to Power BI and was having some trouble using measures.

I have a table with multiple columns including:

Sl.No	Date	New Item ID
1	March 2023	A123
2	March 2023	A124
3	April 2023
4	May 2023	A226

I want to create a line graph with the number of new items per month but when I use the count of new items, it shows that there is an item for April 2023 even though there shouldn't be (1 instead of 0).

I tried creating a measure:

IF(ISBLANK(Table1['New Item ID']), 0, DISTINCTCOUNT(Table1['New Item ID']))

However, I was getting an error: A single value for column 'New Item ID' in table 'Table 1' cannot be determined. This can happen when a measure formula refers to a column that only contains many values without specificying an aggregration such as min, max, count or sum to get a single result.

I also tried the measure: DISTINCTCOUNT(Table1['New Item ID']) + 0 and that shows 1 for blank values (like April 2023) as well.

Thank you

[johnt75]

You can use DISTINCTCOUNTNOBLANK(Table1['New Item ID']) + 0 

[lopa30]

Thank you. I tried that as well and it is still showing as 1.

[johnt75]

Are you sure that the value is actually blank, and doesn't have a space character or something in it?

[lopa30]

 tried checking it using Python (pandas) and it is a null value there. Is there a way to check to check on Power BI as well? 

[johnt75]

If you go into data view you can click the down arrow in the column header, that will allow you to filter by value, including blank.

[lopa30]

Thank you! It seems like it is not blank. Is there a way of checking what exactly is included in the "blanks" as in how many empty spaces? The excel file has blank values so I'm not sure how it changed in power BI.

Alternatively, all of the IDs start with A so I thought I could use the measure:

if((containsstring(selectedvalue(table1['New Item ID']), "A")), DISTINCTCOUNT(Table1['New Item ID']), 0) but now I'm getting everything as 0. What am I doing wrong?

Thank you so much!

[johnt75]

You could add a couple of new calculated columns to your table,

Item length = LEN( 'table1'[New Item ID])

New string = "'" & 'table1'[New Item ID] & "'"

that will show you the length of the string and will wrap the string in single quotes so it is easier to see what the value is.

[lopa30]

I tried this as well and it says that the length of the "blanks" are 0.

[johnt75]

It might be worth looking in Power Query, see if you can replace the empty string with null. In the Replace Values dialog, leave the Value to Find field empty and type null into the Replace With.

[lopa30]

Thank you so much for all the help! I'm not sure if it's the cleanest solution but it works.

I ended up creating a calculated column using:

New Item ID Clean = IF(CONTAINSSTRING(Table1[New Item ID], "A*")), Table1[New Item ID], BLANK()

and then used the measure DISTINCTCOUNTNOBLANK(Table1['New Item ID Clean']) + 0 

[lopa30]

But unlike other columns which clearly say (Blank) when filtering this just is white.