Using Dax Measure to count all rows in a single column except for one specified value

Question

Hi all,&nbsp;I will keep this short in the first instance as I have had trouble signing in to the board (my last post got eaten).Essentially, I have a large dataset of weekly staff activity, collated from multiple teams, with teams and names down the rows and weekly submissions along columns.&nbsp;I have unpivoted my dataset in powerquery so that all my values are running down a couple of columns; team and staff member (now multiple per person), date, activity, time spent on that activity.&nbsp;I have used this to create a dashboard of break down this info, however have been trying to also include a data completion stat of some sort to show how much stuff is missing over a given selection. Within the activity column I have a value "No Data Submitted" that I have used a measure to track against the total in a gauge as a temporary solution, however I am sure there must be another way of doing it that tells me how much complete data I have rather than how much incomplete I have. I have been trying to find a dax function that will run a count of all entries except for the "no data submitted" value, however nothing has worked so far. Any suggestions appreciated.&nbsp;&nbsp;

greg_deckler · Accepted Answer

VM_0451&nbsp;Without sample data and expected output I can only guess but it sounds like:
Measure = COUNTROWS(FILTER('Table',[Activity] &lt;&gt; "No Data Submitted"))

or

Measure = CALCULATE(COUNT('Table'[Name]),[Activity] &lt;&gt; "No Data Submitted")
If this is not it,&nbsp;@ me in a reply and provide sample data and expected output.&nbsp;

Forum Discussion

Using Dax Measure to count all rows in a single column except for one specified value

2 Replies

Related content

Single Measure Alert

Measure Calculates Correctly but Repeats Across Every Single TransactionID

Single select slicer

Measure = single value cannot be determined

how to create single totals