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

[VM_0451]

Hi all,

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.

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.

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.

[Greg_Deckler · Accepted solution]

@VM_0451 Without sample data and expected output I can only guess but it sounds like:

Measure = COUNTROWS(FILTER('Table',[Activity] <> "No Data Submitted"))

or

Measure = CALCULATE(COUNT('Table'[Name]),[Activity] <> "No Data Submitted")

If this is not it, @ me in a reply and provide sample data and expected output. 

[VM_0451]

Yep, that did it.

I got close with my previous attempts but messed up the syntax by not knowing about <>

Thanks very much!