cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Sum multiple columns values, how to?

I would like to ..

Obtain the total count of blank values in Column A

These are true blank values.

Obtain the total count of blank values in Column B

Then add the values. and store the Total in a new field.

So far I have done the following, however not sure if this is best practice.

CountBlankSystem1EmployeeID = COUNTBLANK('Table'[System 1 Employee ID])

Column Formatted as Whole Number

Returns the number 39

CountBlankSystem2EmployeeID = COUNTBLANK('Table'[System 2 Employee ID])

Column Formatted as Whole Number

Returns the number 17

Now I need to work out how to add the values of 39+17 = 56

Note these totals will update with new data uploads from Excel.

TIA

3 REPLIES 3
Super User

@dd88 , Create a new measure

[CountBlankSystem1EmployeeID] +[CountBlankSystem2EmployeeID]

or create a measure

countows(filter(Table, isblank('Table'[System 1 Employee ID])) || isblank('Table'[System 2 Employee ID])) ) )

Helper V

and another question is ..

I currently have the following counts for

Total Mismatches = 4

TotalBlank EmployeeID System 1 = 1

TotalBlank EmployeeID System 2 = 1

The last count to workout is total mistmatches. ie (EmployeeID System 1 is not blank) AND (EmployeeID System 2 is not blank) = 2

How can I achieve this?

Create a new calculated column, new measure?

Eg Total Mismatches – (TotalBlank EmployeeID System 1 - TotalBlank EmployeeID System 2)

Note: ideally there is a unique employeeID that is the key. Unfortunately the data is from active legacy separate systems. I am performing a data clean-up across both systems. To do data migration/ preparation into a new system which will have a unique employeeID.

An example data set is

TIA

Helper V

Many thanks @amitchandak

I used your suggestion Option 1

Create a new calculated column
Total = [CountBlankSystem1EmployeeID] +[CountBlankSystem2EmployeeID]

And it worked! Many thanks again ..

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.