3

My table includes bunch of duplicated sales information for individual sellers, and I basically created a column and a measure which flags users whether the view they have includes duplicated sales information.

here is a very simplified example of what my table looked like

SalesManager  SalesPersonnel   Acccount   Product  Revenue
SalesManager1 SalesPersonnel1 Acccount_A Product_A 100000
SalesManager1 SalesPersonnel1 Acccount_B Product_C 100000
SalesManager1 SalesPersonnel3 Acccount_A Product_A 100000
SalesManager2 SalesPersonnel3 Acccount_B Product_C 100000
SalesManager1 SalesPersonnel2 Acccount_B Product_C 100000
SalesManager1 SalesPersonnel2 Acccount_B Product_C 100000
SalesManager4 SalesPersonnel4 Acccount_B Product_A 100000
SalesManager4 SalesPersonnel4 Acccount_A Product_D 100000
SalesManager4 SalesPersonnel5 Acccount_A Product_B 100000
SalesManager4 SalesPersonnel5 Acccount_A Product_A 100000

I then created a column 

=Acccount&Product&Revenue

This is an extremely simplified example, in my real workbook, I have 30+ columns that I have to combine.

and a measure

= if(CALCULATE(DISTINCTCOUNT([ConsldforDupeCheck]))=COUNTROWS(Table),"","*PossibleDoubleCountError*"

This has been working quite well, except I found that the calculated column that combines bunch of the columns is causing the file size to double...

the only solution I can think of is if I can move the calculated column into measure, but I cannot think of a way to use distinctcount on multiple columns.

Is this possible?

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
user2669043
  • 97
  • 1
  • 6
  • 12

1 Answers1

5

Try this:

My Distinct Count := COUNTROWS(
 SUMMARIZE('Your Table','Your Table'[Account],'Your Table'[Product],'Your Table'[Revenue])
)
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • This is the way to get a group by on multiple columns. Note that if you have to do this across 30 columns, you may see high memory usage at measure evaluation time. – greggyb Jan 19 '16 at 14:01
  • I actually wonder Greg if this approach in a Tabular model might speed up a distinctcount issue we have? We have a key column that has a cardinality of 200M, so maybe if I split the keys into 3 or 4 columns and then use a formula such as you illustrate above to do the count maybe we'll get a faster result? The cardinality of the constituent columns will be loads less – whytheq Nov 19 '21 at 18:18