Matching rows in two tables

[Anonymous]

Hi,

I have two tables 

Table 1 : 

Id        A      B     C   D

1

2

3

4

5

Table 2 

Id   type 

1      A

1      B

1      C

2      A

2      C 

3      C

3      A 

3      A

Now I want to enter data in table 1 where I want sum of 'type' for each Id from table 2. eg Id = 1  A = 1 

So basically query will match id from table 1 in table 2 and count all the type from table 2 for that id.  I need a dax query for it.

Please help! TIA

[Zubair_Muhammad]

@Anonymous

if you have to use DAX,and the 2 tables are related on ID Column

then you can add calculated columns in Table1 like

A = Calculate(Count(Table2[Type]),Table2[Type]="A")

B = Calculate(Count(Table2[Type]),Table2[Type]="B")

[Zubair_Muhammad · Accepted solution]

@Anonymous

With Query Editor you can simply transform your Table2 into Table1

1) Add a duplicate column for Type

2) Now pivot the Type Column Using Type-Copy Column as VALUES

3) Final Result