Forum Discussion
Countrows from another table
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.
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
3 Replies
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
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.
Please ignore the reply above. I just realized I had "sum" as summarize.
