Forum Discussion

DotU's avatar
DotU
Frequent Visitor
4 years ago
Solved

Count rows while filtering with an inactive relationship

Hi guys,   I have two tables linked with an inactive relationship. Table 1 "Required Files" is the One side with unique values, column name: "Register Code & Mth-Yr" Tabe 2 "Submitted Files" is t...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi DotU ,

     

    Table 1 "Required Files"

    Tabe 2 "Submitted Files"

    Inacitve relationship

     

    Now you could create a calculated column in "Required Files".

    Column =
CALCULATE (
    COUNTROWS ( 'Submitted Files' ),
    FILTER (
        'Submitted Files',
        [Register Code & Run Date] = [Register Code & Mth-Yr]
    )
)

     

     

     

    Best Regards,

    Stephen Tao

     

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

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi DotU ,

 

Table 1 "Required Files"

Tabe 2 "Submitted Files"

Inacitve relationship

 

Now you could create a calculated column in "Required Files".

Column =
CALCULATE (
    COUNTROWS ( 'Submitted Files' ),
    FILTER (
        'Submitted Files',
        [Register Code & Run Date] = [Register Code & Mth-Yr]
    )
)

 

 

 

Best Regards,

Stephen Tao

 

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

 

 

  • DotU's avatar
    DotU
    Frequent Visitor
    4 years ago

    Hi Anonymous ,

    What you provided works like a charm. Thank you ! For my work, I need to put additional filters on the Submitted Files table for:
    File Purpose = "MPR"   and

    File Type = "Threat"

    This is how I modified the formula, it works, but I wanted to check with you if it is the best approach:

    TIFilesSubmitted2 =
    CALCULATE (
    COUNTROWS('Submitted Files'),
    FILTER(
    'Submitted Files',
    'Submitted Files'[Register Code & Run Date] = 'Required Files'[Register Code & Mth-Yr]),
    FILTER('Submitted Files', 'Submitted Files'[File Type] = "Threat"),
    FILTER('Submitted Files', 'Submitted Files'[File Purpose] = "MPR"))

    A question I have with regards to this is, why do we have to use the FILTER function inside calculate - ie, shouldn't calculate be able to support filtering as the 2nd argument onwards by itself... I think its because of referring to another table, but I'm not sure how or why that matters cause we could have built the same as a measure instead of a column inside the Required Files table... now maybe im confusing myself too much, but any suggestions and insights would be appreciated!


    On another note,
    The following is what I was able to do before I received your response:

    No of TI Files Submitted =
    var _NoOf_TI_Files = CALCULATE(COUNTROWS('Submitted Files'),'Submitted Files'[File Type] = "Threat",'Submitted Files'[File Purpose] = "MPR", USERELATIONSHIP('Required Files'[Register Code & Mth-Yr],'Submitted Files'[Register Code & Run Date]))

    RETURN
    IF(ISBLANK(_NoOf_TI_Files),0, _NoOf_TI_Files)

    Is my approach safe or would you suggest to avoid it?

    Once again, many thanks!