topic Re: Count of blanks showing up as 1 but want 0 in DAX Commands and Tips

Count of blanks showing up as 1 but want 0

lopa30 — Mon, 05 Jun 2023 12:38:29 GMT

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

Re: Count of blanks showing up as 1 but want 0

johnt75 — Mon, 05 Jun 2023 13:10:06 GMT

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

Re: Count of blanks showing up as 1 but want 0

lopa30 — Mon, 05 Jun 2023 13:50:43 GMT

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

Re: Count of blanks showing up as 1 but want 0

johnt75 — Mon, 05 Jun 2023 13:58:54 GMT

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

Re: Count of blanks showing up as 1 but want 0

lopa30 — Mon, 05 Jun 2023 16:22:00 GMT

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?

Re: Count of blanks showing up as 1 but want 0

johnt75 — Tue, 06 Jun 2023 08:09:59 GMT

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.

Re: Count of blanks showing up as 1 but want 0

lopa30 — Tue, 06 Jun 2023 15:32:21 GMT

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!

Re: Count of blanks showing up as 1 but want 0

johnt75 — Tue, 06 Jun 2023 15:39:41 GMT

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.

Re: Count of blanks showing up as 1 but want 0

lopa30 — Tue, 06 Jun 2023 16:02:47 GMT

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

Re: Count of blanks showing up as 1 but want 0

lopa30 — Tue, 06 Jun 2023 16:06:01 GMT

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

Re: Count of blanks showing up as 1 but want 0

johnt75 — Tue, 06 Jun 2023 16:09:40 GMT

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.

Re: Count of blanks showing up as 1 but want 0

lopa30 — Tue, 06 Jun 2023 20:11:10 GMT

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