60+m row table count dupes by another column --- any faster processing ways to suggest?

[charleshale]

Hi there.   Let's say I have a table of 2 columns -- Fruit and Type -- and want to count dupes taking into account both columns.  The data might look like this:

TABLE1:

FRUIT             TYPE                   DupeCalc-Col1&2

Apples           RedDelicious         0

Apples           Fuji                        1

Apples           Fuji                        1 

Bananas         Dole                      0

My normal dupes count measure for a *single* column (ie Fruit) goes something like this:

Count Dupes =
Var _Dupescount = Table1[FRUIT]
RETURN

CALCULATE(
COUNTROWS('Table1'),
all(Table1),
Table1'[FRUIT] = _Dupescount
)

However, for 2 columns I typically use something like this:

Count Dupes2 =
Var _Dupescount = Table1[FRUIT]

RETURN
CALCULATE(Distinctcount(Table1[Type]),
all(Table1),
Table1'[FRUIT] = _Dupescount
)

Here's my issue:   the first formula works super fast.   The second slows to days on a 100m row table.    Any advice for ways to improve the performance of the 2nd measure?    Would countrows with 2 filters be faster?

[lukiz84 · Accepted solution]

Ok, thought so 🙂

"easiest" way to have the same performance as your first solution (with one column) is to just add another column 

SearchColumn := Table[FRUIT] & Table1[TYPE]

and 

Count Dupes =
Var _Dupescount = Table1[SearchColumn]
RETURN

CALCULATE(
COUNTROWS('Table1'),
all(Table1),
Table1'[SearchColumn] = _Dupescount
)

[lukiz84]

Are you sure thats a measure and not a calculated column?

[charleshale]

Apologies - YES - I mean calculated column 

[lukiz84 · Accepted solution]

Ok, thought so 🙂

"easiest" way to have the same performance as your first solution (with one column) is to just add another column 

SearchColumn := Table[FRUIT] & Table1[TYPE]

and 

Count Dupes =
Var _Dupescount = Table1[SearchColumn]
RETURN

CALCULATE(
COUNTROWS('Table1'),
all(Table1),
Table1'[SearchColumn] = _Dupescount
)

[charleshale]

Thank you.    So to your knowledge figuring out a countrows + filter wouldnt solve the issue much ----- so instead I should concat and then try a single column --- which is what I figured

[lukiz84 · Accepted solution]

Distinctcount is not what you want I think...

But if you don't want an extra column you could also do this:

DupeCount:=
   VAR fruit = Table1[Fruit]
   VAR ftype = Table1[Type]
   RETURN
       CALCULATE(
          COUNTROWS(Table1),
       	  FILTER(
             ALL(Table1),
	     Table1[Fruit]=fruit && Table1[Type] = ftype
          )
        )