Forum Discussion

TJKF's avatar
TJKF
Frequent Visitor
4 years ago
Solved

Creating new table from existing two tables

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:

 

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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.

6 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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's avatar
      TJKF
      Frequent Visitor
      4 years ago

      This works as supposed, thank you! 🙂

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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's avatar
      TJKF
      Frequent Visitor
      4 years ago

      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's avatar
        johnt75
        Super User
        4 years ago

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

    • TJKF's avatar
      TJKF
      Frequent Visitor
      4 years ago

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