topic Using Dax Measure to count all rows in a single column except for one specified value in DAX Commands and Tips

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

VM_0451 — Sat, 12 Sep 2020 10:21:56 GMT

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.

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

Greg_Deckler — Sat, 12 Sep 2020 11:47:03 GMT

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.

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

VM_0451 — Sat, 12 Sep 2020 13:14:06 GMT

Yep, that did it.

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

Thanks very much!