New Calculated Column that checks another column for distintc values

[GuilleATT]

Hi,

really appreciate some help here.

I have a Sales table like so:

SalesID	Product
A	Shoes
B	Shirt
B	Shoes
C	Shirt
C	Shirt

and I need to add a calculated column so that I know if a certain sale has two or more different products in it ("Combined"):

SalesID	Product	Category
A	Shoes	Shoes
B	Shirt	Combined
B	Shoes	Combined
C	Shirt	Shirt
C	Shirt	Shirt

I have tried with DistinctCount, SUMs and IF's with no luck,

Thank you in advanced!

Guille

[jthomson]

Wouldn't try this as a calculated column, it'd be pretty inefficient, unless you're asking in the DAX forum as you can't use Power Query. In that, I'd just duplicate your table, then group on your sales ID with a distinct count of the product as aggregation. You can then merge that in and use conditional "if merged value = 1 then existing value else combined" type of logic.

Probably can be done in DAX with a bunch of filter manipulation

[GuilleATT]

Hi, thanks for your answer, will try this as well to check for effiency.

[Greg_Deckler · Accepted solution]

@GuilleATT - Perhaps:

Category Column = 
  VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[SalesID]=EARLIER([SalesID])),[Product])
RETURN
  IF(COUNTROWS(__Table)>1,"Combined",[Product])

[GuilleATT]

That worked really nicely, thanks! I will study these functions