How do we get count of blank values if more than two column conditions

[saivina2920]

I am trying to get count of blank values which i used somewhere in SQL. But, the same scenario i am going to use in POWER BI DAX.

will it work the below DAX in power bi.

=DISTINCTCOUNT(DISTINCT SALE_REF_NUMBER,SALE_PLACE= len(trim(SALE_PLACE)) =0,SALE_RETURN = len(trim(SALE_RETURN))=0,SALE_QTY = len(trim(SALE_QTY)) =0)

[PhilipTreacy]

Hi @saivina2920 

To check your column in DAX, create a new column and use the LEN function, for example :

Column = LEN([EMP_PLACE])

If you find that the 'blank' values have a length then this may be just space characters.  You can remove these using the TRIM function in which case you could use this DAX to work out the count you want

Blank_Count = 

VAR __EMP_PLACE_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', ISBLANK(TRIM('Table'[EMP_PLACE])=0)))
VAR __EMP_COUNTRY_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', ISBLANK(TRIM('Table'[EMP_COUNTRY])=0)))
VAR __EMP_REMARKS_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', ISBLANK(TRIM('Table'[EMP_REMARKS])=0)))

RETURN

__EMP_PLACE_COUNT + __EMP_COUNTRY_COUNT + __EMP_REMARKS_COUNT

If none of this works you may have someother non-printing characters in the column, like TAB characters.

To check this, create a column in DAX and use this code

Column1 = UNICODE([EMP_PLACE])

which will give you the numeric code for the character.  If you get to this point let me know what that code number is.

If you still haveno success please post some sample data/supply your PBIX to make it easier for me to work out.

Regards

Phil

[PhilipTreacy]

Hi @saivina2920 

Download sample PBIX file

This works with my sampledata in attached file.  If your values really are BLANK then change  LEN('Table'[Column])=0 to ISBLANK('Table'[Column]) in this measure

Blank_Count = 

VAR __EMP_PLACE_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', LEN('Table'[EMP_PLACE])=0))
VAR __EMP_COUNTRY_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', LEN('Table'[EMP_COUNTRY])=0))
VAR __EMP_REMARKS_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', LEN('Table'[EMP_REMARKS])=0))

RETURN

__EMP_PLACE_COUNT + __EMP_COUNTRY_COUNT + __EMP_REMARKS_COUNT

regards

Phil

[saivina2920]

Sorry for the late response. yours is correct if we calculate and count of every column values

I checked the blank and it's null value only. 

But, i want to apply filter. so that i can get the correct count.

i tried below, but, it's showing error.

VAR TCOUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]),FILTER(Table, 
Table[EMP_PLACE]=LEN(Table[EMP_PLACE])=0 && 
Table[EMP_COUNTRY]=LEN(Table[TN_FROM])=0 && 
Table[EMP_REMARKS]=LEN(Table[TN_TO])=0 
))
RETURN TCOUNT

Will the above work if i used all in one filter

[PhilipTreacy]

@saivina2920 

Need more info.  What's the error?  The columns in your latest DAX are different to the columns you showed in your image.  What's in them?

Can you supply some data? Really hard to help you without knowing what data you're working with.

Regards

Phil

[saivina2920 · Accepted solution]

I tried below. but, surprisingly it's working and count is coming perfect. i thing, we can use "" for blank. 

pls. confirm the DAX will work for all the cases.

VAR TCOUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]),FILTER(Table, 
Table[EMP_PLACE]="" &&   // "" ==> will it work for blank data
Table[EMP_COUNTRY]="" && // "" ==> will it work for blank data
Table[EMP_REMARKS]=""  // "" ==> will it work for blank data
))
RETURN TCOUNT

NOTE

I filtered using manual as you given sample data. it's like below and total count is almost "6".

[saivina2920]

Really sorry for the wrong column name.

pls. find the correct column as below with DAX.

VAR TCOUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]),FILTER(Table, 
Table[EMP_PLACE]=LEN(Table[EMP_PLACE])=0 && 
Table[EMP_COUNTRY]=LEN(Table[EMP_COUNTRY])=0 && 
Table[EMP_REMARKS]=LEN(Table[EMP_REMARKS])=0 
))

RETURN TCOUNT

Error thrown : DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

if i use below i am getting wrong count.

Blank_Count = 

VAR __EMP_PLACE_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', LEN('Table'[EMP_PLACE])=0))
VAR __EMP_COUNTRY_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', LEN('Table'[EMP_COUNTRY])=0))
VAR __EMP_REMARKS_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', LEN('Table'[EMP_REMARKS])=0))


RETURN

__EMP_PLACE_COUNT + __EMP_COUNTRY_COUNT + __EMP_REMARKS_COUNT

so, i decided to use in filter. Because, my earlier other report was using "&&" conditions.

[saivina2920]

pls. let me know if you need any more details.

[saivina2920]

I filtered using manual as you given sample data. it's like below and total count is almost "6".

[saivina2920]

You are really great. I chacked length of blank column data. 

All the blank column of the length showing as "1" instead of "0".

Is it possible to come as "1" for blank values (checkd Text.length)...?

[PhilipTreacy]

Hi @saivina2920 

If your 'blank' values have length 1 then they aren't really blank of course.  You can check for 'blank' values of length 1 with this measure

Blank_Count = 

VAR __EMP_PLACE_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', LEN('Table'[EMP_PLACE])=1))
VAR __EMP_COUNTRY_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', LEN('Table'[EMP_COUNTRY])=1))
VAR __EMP_REMARKS_COUNT = CALCULATE(DISTINCTCOUNT('Table'[EMP_NO]), FILTER('Table', LEN('Table'[EMP_REMARKS])=1))

RETURN

__EMP_PLACE_COUNT + __EMP_COUNTRY_COUNT + __EMP_REMARKS_COUNT

You mention Text.Length which is a Power Query M function, do you want the solutionin DAX or M?

Regards

Phil

[saivina2920]

I am just checking blank value length. for that i am checking and creating customized column Text.Length. There only i found Text.Length which contains blank is 1 (i hope some junk value is there.)

because all the data coming from oracle data base.

But, i really want  solution in DAX...

How do i check whether the column is really blank or having some junk values using DAX...?

[PhilipTreacy]

Hi @saivina2920 

Hard to give you an answer without seeing your data.  What column are you looking for blanks in?

You can count the number of blanks in a column like this

CALCULATE(COUNTROWS('Table'), FILTER('Table', ISBLANK('Table'[Column])))

Please provide some sample data.

Regards

Phil

[saivina2920]

[Ashish_Mathur]

Hi,

Please share some data, define the question in simple English (not an SQL code) and show the expected result.

[saivina2920]

pls find image data for your reference.

take count of 3 blank columns (which distinct of EMP_NO (because EMP_NO, it not unique)

[Ashish_Mathur]

Hi,

One idea would be to replace all blanks with NA using the Query Editor.  Thereafter, right click on the first column and select "Unpivot other columns".  Then drag the Emp_no column to the table visual and write this measure

Measure1 = calculate(countrows(data),data[value]="na")

Hope this helps.

[saivina2920]

pls. let us know if you need any more details.