DistinctCount and IIF

• mlamour

  Grasshopper

  Points: 13

  More actions

  March 1, 2010 at 8:21 am

  #386412

  Hi,

  I am trying to get a distinct count of records that have the value of "yes" in particular field. The field in particular can have a yes or no value. I tried to do distinctcount(fields!Comp.value) of records but it does not giving me the number of records with the "yes" value correctly.

  I also tried =distinctcount(iif(fields!Comp.value ="yes",)

  For example

  RowID Comp

  1 no

  2 yes

  3 no

  4 yes

  5 no

  Yes Total 2

  Any assistance is appreciated.

• Dirk Wegener

  Ten Centuries

  Points: 1161

  More actions

  March 9, 2010 at 5:36 am

  #1130570

  Create a named calculation in your dsv

  case Comp when 'yes' then 1 else 0 end

  and create a regular measure on this named calculation with SUM as aggregate function.

  http://www.dwexplorer.com
  @dtwegener