unique count

[kin1]

i'm trying unique count from a column of data. each unique record will count as one. the image i pasted shows 2 of everything. does anyone know if i cant just count the same value as one? the same value can be listed more than 2 times, sometimes it can be as much as 20.

thanks

 

[scottsen]

Sorry, can you add what you want the measure to return for each month & grand total?

As we have it now, it should show "1" for each month, and for the grand total... but I'm not sure how you want it.

 

[kin1]

yes it does show 1 for each month, is there a way to only have it show for the earliest month? out of the example, there should only be a 1 count for the month of august.

 

[scottsen]

Hmm... in that case it might be easier to deal with this in a calculated column. Not sure.

I can imagine a measure like... uh... hrm...

=IF(MIN(MyTable[Date]) = CALCULATE(MIN(MyTable[Date]) ALL(Calendar)), 1, 0)

If the smallest date in THIS pivot cell, for this job is equal to the smallest date in the WHOLE calendar... return 1, else 0. Untested theory.

For a calc column, you could try finding the smallest date associated with each job #.

=CALCULATE(MIN(MyTable[Date]), FILTER(MyTable, MyTable[Job#] = EARLIER(MyTable[Job#])))

Then, maybe use that in a unique count measure?
=CALCULATE(DISTINCTCOUNT(MyTable[Job#]), FILTER(MyTable, MyTable[Date] = MyTable[FirstDate]))

All untested -- apologies a bit too much client work right now to give this proper attention.