finding unique rows in powerpivot

[rspaeth2001]

I need to count clients served each day.
I want to only count a client one time even if they have multiple services in one day.

I have a formula that works in Excel
I have Column A is a date field and Column B is Client ID

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Client ID[/TD]
[TD]1 per Day Visit(formula)[/TD]
[/TR]
[TR]
[TD]7/1/2015[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7/1/2015[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7/1/2015[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7/2/2015[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7/2/2015[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

This formula works for small data sets. (Monthly)
But, I run out of memory if try for 6 months or a year's worth of data.

I can query the server and get the dataset I want but can't get the formula to work in powerpivot.
I don't want a Total Summary field of Total Visits Measure calculation.
I would prefer to go row by row to verify results.

Can you help me convert this formula to a Calculated Column?


Thanks!

 

[Matt Allington]

You should not use a calculated column. Read my blog about that here Calculated Columns vs Measures in DAX - Excelerator BI

try a measure =DISTINCTCOUNT(tableName[Client ID])

 

[rspaeth2001]

You should not use a calculated column. Read my blog about that here Calculated Columns vs Measures in DAX - Excelerator BI

try a measure =DISTINCTCOUNT(tableName[Client ID])

Thank you Matt. I have read your book 'Learn to Write DAX' and found it excellent.
(Unfortunately, I've been doing so much SSRS lately, I need a review)
I have used the DISTINCTCOUNT but that just gives me a summary in the pivot table.
I would like to see the count row by row in the raw data, so I can demonstrate what is happening to Management.
That is why I was hoping for a calculated column that operated like the Array function above, to demonstrate the results, rather than just have a total.

Bob

 

[ImkeF]

You can do that in Power Query instead and load your table from there to your Power Pivot data model:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index = Table.AddIndexColumn(Source, "Index", 0, 1),
Group = Table.Group(Index, {"Date", "Client ID"}, {{"Count", each List.Min([Index]), type number}}),
#"Added Custom1" = Table.AddColumn(Group, "Lookup", each 1),
#"Merged Queries" = Table.NestedJoin(Index,{"Index"},#"Added Custom1",{"Count"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Lookup"}, {"Lookup"}),
#"Sorted Rows" = Table.Sort(#"Expanded NewColumn",{{"Index", Order.Ascending}})
in
#"Sorted Rows"

 

[rspaeth2001]

You can do that in Power Query instead and load your table from there to your Power Pivot data model:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index = Table.AddIndexColumn(Source, "Index", 0, 1),
Group = Table.Group(Index, {"Date", "Client ID"}, {{"Count", each List.Min([Index]), type number}}),
#"Added Custom1" = Table.AddColumn(Group, "Lookup", each 1),
#"Merged Queries" = Table.NestedJoin(Index,{"Index"},#"Added Custom1",{"Count"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Lookup"}, {"Lookup"}),
#"Sorted Rows" = Table.Sort(#"Expanded NewColumn",{{"Index", Order.Ascending}})
in
#"Sorted Rows"

I'm afraid I'm a little confused by this answer.
I pasted the results into Table1 of Excel
I then created a PowerQuery from that Table

I tried Adding a column and pasting your statement in the formula bar - changed Row Headers to BEGIN_DATE, CLIENT_ID
but still get
Expression.Error: The column '[BEGIN_DATE]' of the table wasn't found.Details:
[BEGIN_DATE]

 

[rspaeth2001]

I think i got it. Thanks When I replaced "Date" with "[BEGIN_DATE]" (from available columns) it didn't work. I removed the brackets "BEGIN_DATE" and success.
Thank you!!