Countrows from another table

[Kylie1920]

Hello, 

I've been working on this calculation for a while and couldn't get the right results. Please help. 

Table 1

Notification   File
B28346          F12
E46284          B05
B28346          F12
B28346          D98
FG4782          B05
B29390          ---
Heke20          --

Table 2

Notification
B28346
E46284
B28346
B28346
FG4782

These two tables are many-to-many relationship. I need to create a calculation in Table 2.
To find the count of file per notification, if file is null, result should be 0.

I have tried:
1) CALCULATE(COUNTROWS(DISTINCT(table1[FILE])),ALLEXCEPT('table1','table1'[notification]),FILTER(Pictures,Pictures[FILE] <> ""))
2) CALCULATE(SUM(table1[count_of_id]),ALLEXCEPT(table1,table1[NOTIFication]))

Thanks for your helps. 

[v-yangliu-msft · Accepted solution]

Hi  @Kylie1920 ,

Here are the steps you can follow：

1. Create calculated column in Table1.

COUNT = CALCULATE(DISTINCTCOUNT('Table1'[File]),FILTER(ALL(Table1),'Table1'[Notification]=EARLIER('Table1'[Notification])))

2. Create calculated column in Table 2.

Column =
IF(
    'Table2'[Notification] IN SELECTCOLUMNS('Table1',"1",'Table1'[Notification]),
CALCULATE(MAX('Table1'[COUNT]),FILTER(ALL(Table1),'Table1'[Notification]='Table2'[Notification]))
   ,0)

3. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

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

[Kylie1920]

Hello Liu, 

I was able to create the "count" and the "column" for both tables 1 and 2. However, the issue occurs when table 2 has more than one record of notifications. The calculation results tend to multiply by the line of records. 

For example: 

Table 1: 

Notification   File              "count" 

B2834            83746          1

Table 2: 

Notification        "column" 

B2834                  6 

How do I avoid the "column" from multiplying? Table 2 data has 6 records of notifications B2834.

[Kylie1920]

Please ignore the reply above. I just realized I had "sum" as summarize.