Count of Distinct Values from a column

[Mavericks334]

Hi,

I have a table that has the below data in columns
[TABLE="width: 202"]
<COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY>[TR]
[TD="class: xl66, width: 160, bgcolor: transparent"]Preparer[/TD]
[TD="class: xl67, width: 109, bgcolor: #8db4e2"]Completed Date[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Sobkowiak, Bartosz[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]8/1/12 1:03 AM[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Sobkowiak, Bartosz[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8/1/12 1:03 AM[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Sobkowiak, Bartosz[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]8/1/12 1:03 AM[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8/1/12 1:04 AM[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]8/1/12 1:04 AM[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8/1/12 1:04 AM[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Wegielska, Anna[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]8/1/12 1:04 AM[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8/1/12 1:04 AM[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Wegielska, Anna[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]8/1/12 1:04 AM[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Zalewska, Malgorzata[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8/1/12 1:04 AM[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Zalewska, Malgorzata[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]8/1/12 1:05 AM[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Dobruchowska, Edyta[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8/1/12 1:22 AM[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Dobruchowska, Edyta[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]8/1/12 1:22 AM[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Wlodarczak, Malgorzata[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8/1/12 1:22 AM[/TD]
[/TR]
</TBODY>[/TABLE]

I need to caculate the unique no.of days the person has worked.

I created a measure =COUNTROWS(DISTINCT('SharePoint Data'[Completed Date])), the problem with this is that it counts all the days, since there is a time stamp. how do i create a measure so that it would only count the data.

 

[texasalynn]

see if this works

Excel 2003
	A	B	C	D	E	F
1	Name	Completed Date			Name	Counts
2	Sobkowiak, Bartosz	8/1/2012 1:03			Sobkowiak, Bartosz	2
3	Sobkowiak, Bartosz	8/2/2012 1:03			Rozmarynowska, Joanna	1
4	Sobkowiak, Bartosz	8/1/2012 1:03			Wegielska, Anna	1
5	Rozmarynowska, Joanna	8/1/2012 1:04			Zalewska, Malgorzata	2
6	Rozmarynowska, Joanna	8/1/2012 1:04			Dobruchowska, Edyta	1
7	Rozmarynowska, Joanna	8/1/2012 1:04			Wlodarczak, Malgorzata	1
8	Wegielska, Anna	8/1/2012 1:04
9	Rozmarynowska, Joanna	8/1/2012 1:04
10	Wegielska, Anna	8/1/2012 1:04
11	Zalewska, Malgorzata	8/1/2012 1:04
12	Zalewska, Malgorzata	8/1/2012 1:05
13	Dobruchowska, Edyta	8/1/2012 1:22
14	Dobruchowska, Edyta	8/1/2012 1:22
15	Wlodarczak, Malgorzata	8/1/2012 1:22
Sheet1

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF(A$2:A$21=E2,MATCH(B$2:B$21,B$2:B$21,0)),ROW(B$2:B$21)-ROW(B$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

copy the formula down

 

[Mavericks334]

Thanks for the abobe formula, it would work fine in excel, however i require a formula to work in powerpivot.

 

[pete234]

I believe if you create another column to calculate your date field into a time intelligent function you can use your formula.
What you want is just the date so look at this page to get rid of the time value in your column so the DISTINCT will look at that level and ignore time.
DATE Function
Make your formula refer to the new calculated field.

 

[Mavericks334]

Hi,

I have created another field and used INt(Date Column) to convert the date, the problem is i am increasing the size of my file, i need to creature a measure to do the same, so the file size does not increase much.

 

[Laurent C]

It is probably a best practise to actually split your date time columns in date and time.

The easiest way - in Excel and Powerpivot - to get the date from a datetime is to truncate it using the INT function.

 

[Mavericks334]

I have already used that, trying to see whether i could avoid creating an addtional column, by creating a measure, which would reduce the size of my sheet.

 

[Laurent C]

In fact, according to the author of this book, splitting your date time value in two columns may lead to saving memory consumption, due to the way PowerPivot is working.

When I say that I mean importing two columns in PowerPivot instead of one, not adding two additional columns on top of the combined datetime column.

Also, if you create a date table, then you will need the date (without time) to build a relationship.

 

[Mavericks334]

The problem that i am facing is i am unable to get the max unique count of days, because the date is one column and i need to calculate the max dates a person has worked, because the person could i be in column A or cloumn B

 

[Fazza]

hi,

I don't have Power Pivot and am unfamiliar with it. Maybe though it helps to know a solution for earlier versions of Excel? If I understand the question correctly, this SQL does what you want. I tested in Excel 2003. I'm guessing this can be used in Power Pivot. HTH

SELECT YourNameField, COUNT(*) AS [YourCount]
FROM 
(SELECT DISTINCT YourNameField, INT(YourDateField)
FROM your_table_name)
GROUP BY YourNameField