Adding new column with a grouped value.

[PavelAndreev]

Hi everyone,

I have the following sample file:

Segment	Country	Product	Discount Band
Government	Germany	Carretera	None
Government	Germany	Paseo	None
Government	Canada	Paseo	None
Government	Germany	Velo	None
Government	France	VTT	None
Government	France	Amarilla	None
Government	Mexico	Carretera	Low
Government	France	Carretera	Low
Government	France	Paseo	Low

I want to do the following:

• Create additional column which includes distinct q-ty of products for each country. The result should be like in the table below: For each row of the table: If Country="Germany", then 3; If Country="France", then 4; If Country="Canada", then 1; If Country="Mexico", then 1. 

Question: Is it possible to add such column to the existing table? How?

Segment	Country	Product	Discount Band	Q-ty of products
Government	Germany	Carretera	None	3
Government	Germany	Paseo	None	3
Government	Canada	Paseo	None	1
Government	Germany	Velo	None	3
Government	France	VTT	None	4
Government	France	Amarilla	None	4
Government	Mexico	Carretera	Low	1
Government	France	Carretera	Low	4
Government	France	Paseo	Low	4

• Create a measure which returns the amount of countries which have more than 1 product. The result should be 2 (Canada and Mexico)
  Question: Is it possible to create a measure without creating new table.

I did it in the following way, but I want to improve it:

• Create a new table (answer to the first question):

NewTable = 
		VAR t1 = 
			SUMMARIZECOLUMNS('Sample'[Country] , 'Sample'[Product])
		VAR t2 = 
			GROUPBY(t1,
			'Sample'[Country],
			"NUmofProducts", COUNTX(CURRENTGROUP(),1))
		RETURN
			t2

• Create a new measure for the NewTable (answer to the second question):

NewMeasure = 
    CALCULATE(COUNTX(NewTable,NewTable[NUmofProducts]),NewTable[NUmofProducts]>1)

How can I receive the same results without creating a new table?
Thanks in advance!

[ryan_mayu · Accepted solution]

@PavelAndreev 

what's the purpose of create the addtional column? you didn't use it when you create a new table

you can try this measure

Measure =

VAR tbl=SUMMARIZE('Sample','Sample'[Country],"product",count('Sample'[Product]))

return COUNTROWS(FILTER(tbl,[product]>1))

[Greg_Deckler · Accepted solution]

@PavelAndreev You could create a new column in your existing table like this:

Column = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Country]=EARLIER('Table'[Country])),"Product",[Product])))

[PavelAndreev]

@Greg_Deckler , simply brilliant! Thanks a lot!

[ryan_mayu · Accepted solution]

@PavelAndreev 

what's the purpose of create the addtional column? you didn't use it when you create a new table

you can try this measure

Measure =

VAR tbl=SUMMARIZE('Sample','Sample'[Country],"product",count('Sample'[Product]))

return COUNTROWS(FILTER(tbl,[product]>1))

[PavelAndreev]

@ryan_mayu , the column is needed for other purposes. Your new measure solution is great! Thanks! 

[ryan_mayu]

you are welcome