Create hierarchical bridge table

Anonymous — Fri, 12 Feb 2021 17:24:14 GMT

I need to create a M:M link between two tables with similar structures. (One is historical data. The other is forecast data.) The data is categorized by Market and Submarket, like this:

Market	Submarket	Region	Sales
Retail	Grocery	NA	247
Retail	Grocery	EU	656
Retail	Grocery	AP	723
Retail	Department Stores	NA	912
Retail	Department Stores	EU	719
Retail	Department Stores	AP	206
Retail	Online	NA	861
Retail	Online	EU	818
Retail	Online	AP	919
Financial	Banking	NA	231
Financial	Banking	EU	326
Financial	Banking	AP	299
Financial	Capital Markets	NA	288
Financial	Capital Markets	EU	982
Financial	Capital Markets	AP	287
Financial	Insurance	NA	243
Financial	Insurance	EU	805
Financial	Insurance	AP	534

What I want is a table with distinct values of submarket grouped by market, like this:

Market	Submarket
Financial	Banking
Financial	Capital Markets
Financial	Insurance
Retail	Department Stores
Retail	Grocery
Retail	Online

I am not trying to summarize any values. I just want the hierarchical data structure. I've tried the SUMMARIZE and CALCULATETABLE functions, but I'm not able to get the result I'm looking for.

Re: Create hierarchical bridge table

PaulDBrown — Fri, 12 Feb 2021 17:30:19 GMT

Anonymous

SUMMARIZE works:

Re: Create hierarchical bridge table

Anonymous — Fri, 12 Feb 2021 19:48:37 GMT

PaulDBrown Wow, that was a lot easier than I was making it. I somehow thougth I needed a summaried field when using the SUMMARIZE function.
Thanks very much for your help!

Re: Create hierarchical bridge table

CNENFRNL — Sat, 13 Feb 2021 00:31:43 GMT

topic Re: Create hierarchical bridge table in DAX Commands and Tips

Create hierarchical bridge table

Re: Create hierarchical bridge table

Re: Create hierarchical bridge table

Re: Create hierarchical bridge table