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 the Many side with column "Register Code & Run Date" which can be repeated or just not present when compared to table 1 (so we know the file is not yet submitted).

I want to count how many times a file is submitted in Table 2 and bring that information into a new column in Table 1.

 

If I had an active relationship, this would be a simple measure = COUNTROWS(Table 2)
Placing the measure inside Table 1 automatically causes table 2 to filter for each "Register Code & Month-Yr" and count only those rows.

How do I replicate the same with an inactive relationship?

I've tried to use USERELATIONSHIP, but I don't know where I'm going wrong.

No of Files Submitted =
var _NoOfFiles = CALCULATE(COUNTROWS('Submitted Files'), USERELATIONSHIP('Required Files'[Register Code & Mth-Yr],'Submitted Files'[Register Code & Run Date]))

RETURN
IF(ISBLANK(_NoOfFiles),0, _NoOfFiles)


Thanks in advance for your help!

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.

     

     

5 Replies

  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    4 years ago

    hi DotU 

    I see you have a dimension RequiredFiles and the column used for the relationship is Register Code & Mth.
    Register Code & Mth : Is it some kind of concatenation of two fields?

    What is the field used to define a RequiredFile?

    • DotU's avatar
      DotU
      Frequent Visitor
      4 years ago

      Hi m3tr01d ,

      Thanks for the prompt response. Yes, I was concatenating two fields. And when I went into my file to send you some screen captures, I realized that in one table I concatenated using "-" while in the other I used " - ", and because of this any and all formuals I was trying were failing!

      I'm surpised PowerBI even let me create a relationship if there were zero matches... hummm

  • 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!