Help With Blank Values Follow-up

[jereaallikko]

Hi all,

I need help with DAX or custom column. I am following mandatory fields that my team needs to fill. So, if some field is missing, a table shows blank value on a cell. 

Basically, I would like a card visual to show number of blank values in a whole dataset containing around 20 columns. 

Sample data and table illustrated below:

So, totally 15 cells are blank, and need to be filled. 

Additionally, I would like to create a visual that shows the number of blank values for each person.

Like:

James: 3

Jack: 1

Fernando: 3

Kate: 4

Robert: 4

Any suggestions how the problem could be solved? If more information is needed, don't hesitate to ask.

Thanks,

Jere Aallikko

[v-xiaotang · Accepted solution]

Hi @jereaallikko 

You can take steps below for reference.

1. Go to Power Query Editor, you need to select all the columns, then

Get

2. Repeat the operation, and replace “List.NonNullCount” with “List.Count”

3. Add a custom column to get a diff, and change its type to Whole Number

4. Remove the two columns if you want

5. Create a measure

count = CALCULATE(SUM('Table'[Blank]),GROUPBY('Table','Table'[reporter]))

Hope it helps.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@jereaallikko 

You can add custom column using dax to the the table as follows:

Number of Blanks = 
COUNTROWS(
 FILTER( {TABLE02[ID], TABLE02[Amount], TABLE02[Created], TABLE02[Status], TABLE02[Department] , 
 TABLE02[Product]}, [Value] = BLANK() )
)

Insert a card visual and include the newly created column, Use a Table visual to include Reporter Name and the new column to show by name.