Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PavelAndreev
New Member

Adding new column with a grouped value.

Hi everyone,

I have the following sample file:

SegmentCountryProductDiscount Band
GovernmentGermanyCarreteraNone
GovernmentGermanyPaseoNone
GovernmentCanadaPaseoNone
GovernmentGermanyVeloNone
GovernmentFranceVTTNone
GovernmentFranceAmarillaNone
GovernmentMexicoCarreteraLow
GovernmentFranceCarreteraLow
GovernmentFrancePaseoLow

 

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?

SegmentCountryProductDiscount BandQ-ty of products
GovernmentGermanyCarreteraNone3
GovernmentGermanyPaseoNone3
GovernmentCanadaPaseoNone1
GovernmentGermanyVeloNone3
GovernmentFranceVTTNone4
GovernmentFranceAmarillaNone4
GovernmentMexicoCarreteraLow1
GovernmentFranceCarreteraLow4
GovernmentFrancePaseoLow4

 

  • 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!

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@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))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Greg_Deckler
Super User
Super User

@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])))

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@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])))

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler , simply brilliant! Thanks a lot!

ryan_mayu
Super User
Super User

@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))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.