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.

     

     

m3tr01d's avatar
m3tr01d
Continued 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