Creating new table from existing two tables

[TJKF]

Hi,

I have two tables: Market potential and company. I want to create a new table with columns company ID, product group and potential. 

When I use
SUMMARIZECOLUMNS(Company[Company ID], 'Market Potential'[Product Group])
I get the right start for the new table. First column with company ID's and each product group once per compay ID.

Now if I add the potential per company and product group to the DAX:
SUMMARIZECOLUMNS(Company[Company ID], 'Market Potential'[Product Group], 'Market Potential'[Potential])
I get all potential and product group combinations to the new table, not just potential based on the company size.

This is obviously the wrong way to do this, but I can't figure out the right way and don't know what to look for for a solution.

Example of tables and results:

[SpartaBI · Accepted solution]

@TJKF create this table:

RESULT = 
SELECTCOLUMNS(
	NATURALINNERJOIN(
		SELECTCOLUMNS(
			'Market Potential',
			"Company size", [Company size] & "",
			"Product Group", [Product Group],
			"Potential", [Potential]
		),
		SELECTCOLUMNS(
			'Company',
			"Company ID", [Company ID],
			"Company size", [Company size] & ""
		
		)
	),
	"Company ID",[Company ID],
	"Product Group", [Product Group],
	"Potential", [Potential]
)

In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos.

[TJKF]

This works as supposed, thank you! 🙂

[johnt75]

Not sure if this will work but you could try

New Table = CALCULATETABLE(
SUMMARIZECOLUMNS(Company[Company ID], 'Market Potential'[Product Group], 'Market Potential'[Potential]),
TREATAS( VALUES(Company[Company Size]), 'Market Potential'[Company Size])
)

[TJKF]

Adding the VALUES returns the exact same end result as my original result with just SUMMARIZECOLUMNS. I got solution from another reply. Thank you anyway! 🙂

[TJKF]

Hi, thank you. It is not working as it is. TREATAS needs a table expression and I don't know which one to use. I tried with company table, but it has a lot of other columns than in the example and I apparently would have to specify them all? I also tried with the market potential, but I get error SummarizeColumns() and AddMissingItems() may not be used in this context.

As you can see, I just don't understand the logic behind this..

[johnt75]

I edited my original post to include a VALUES around the column, give that a try