Forum Discussion

Kylie1920's avatar
Kylie1920
New Member
4 years ago
Solved

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        ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Kylie1920
New Member
4 years ago

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's avatar
Kylie1920
New Member
4 years ago

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