Forum Discussion

jereaallikko's avatar
jereaallikko
Helper III
5 years ago
Solved

Help With Blank Values Follow-up

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

Need Help
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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.

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    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.