Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
@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])) ) )
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
Many thanks @amitchandak
I used your suggestion Option 1
Create a new calculated column
Total = [CountBlankSystem1EmployeeID] +[CountBlankSystem2EmployeeID]
And it worked! Many thanks again ..
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
110 | |
97 | |
71 | |
67 |