Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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 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.
This works as supposed, thank you! 🙂
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])
)
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! 🙂
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..
I edited my original post to include a VALUES around the column, give that a try
User | Count |
---|---|
94 | |
79 | |
75 | |
64 | |
60 |
User | Count |
---|---|
106 | |
98 | |
77 | |
63 | |
61 |