summarize two tables

[Anonymous]

Hi,

I have two tables,

1. Month,  newspaper company, distribution agency,  distribution address (city),  distribution address (street name)

2. Month,  store name, customerid, customer address (city), customer address (street name)

I want to add  "newspaper company" and " distribution agency" to the second table, 

Problems:

1) Several newspaper companies distribute to the same addresses

2) Newspaper uses more than one agency and some use the same agencies

I tried to summarize them, but it gives me an error message that a table of multiple values was supplied where a single value was expected.

Can anybody help me with this?

[V-pazhen-msft]

@Anonymous 

Summarize is a table function used to create a Table, if you just want to move columns to another table, a column function is expected.

In this case, you should have a relationship between the tables using Month columns, so you can just use Related() to create 2 columns in Table2.

Column 1 = RELATED('Table1'[newspaper company])
Column 2 = RELATED('Table1'[distribution agency])

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Refer:https://radacad.com/append-vs-merge-in-power-bi-and-power-query

You can move the column from one table to another

New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])
New Column = maxx(filter(table1,table1[Col1]= earlier(table1[col1]) && table1[Col1]= earlier(table1[col2]) ),table1[required_col])

[Anonymous]

@amitchandak 

thanks for your answer. "maxx(filter('table1...."   almost worked!

I get the newspaper column, but it only shows for one streetname one newspaper company.

But it has to show the streetname several times, in order to show all the newspaper companies.

streetname A	newspaper company A
streetname A	newspaper company B
streetname A	newspaper company C

@V-pazhen-msft Thanks for your answer, "related" does not work. error message reads, that there is no connection in this context. (I made a connection with date), I also tried "lookupvalue" before, but error shows multiple values where single value expected

[amitchandak]

@Anonymous ,

I need to try with data.Can you share sample data and sample output.

mark me @

Appreciate your Kudos.

[Anonymous]

@amitchandak 

Thank you!

I uploaded some sample data.

https://1drv.ms/u/s!AsNOXcTG8UttiXkxSEs0vUBNz5al?e=QBdUmn

[Ashish_Mathur]

Hi,

Please show the expected result very clearly.

[Anonymous]

@Ashish_Mathur 

Hi, 

I try to explain it in more detail.

I have actually three tables. 1) Newspaper distribution area, 2) customer info (address) , 3) newspaper distribution costs

I want to compare these tables to get the following results.

So, I thought to summarize them in one table is the easiest way.

I want to know:

1) How many customers are coming from areas with no newspaper distribution.

2) How many areas with newspaper distribution, but no customers

3) How much is the cost for each area, each newspaper, each distribution agency, cost per customer

customer ID	City	Ward	Newspaper/Agency	count all customers	count areas with no customers	count of customers with no distribution
	Tokyo	Shinjuku 9	Agency A		1
zzz	Tokyo	Shinjuku 7	Agency C	1
xxx	Tokyo	Shinjuku 10	Agency B	1
yyy	Tokyo	Shinjuku 11		1		1

[amitchandak]

calculatetable is another option example.  You can filter one table by the second table based on need

sumx(summarize(calculatetable(TableA,DATESINPERIOD( DATE[DATE], MAX( DATE[DATE] ), -6, MONTH )),
TableA[ID] , "_active",distinctcount(TableA[ID])),_active)