Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count records in a related table by ID

I have 2 related tables joined on ID. Table1 is a summary table with 1 record per ID and Table2 is a detail table with multiple records per ID. I need a new column in Table1 with a count of records i...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    This might work:

     

    CALCULATE(COUNTAX(Table2,Table2[Value]),Table2[ID])
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Perfect, thanks!

richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Hi Anonymous,

 

I realize that this is solved, however why do you need a new column when you can use a measure like,

count = countrows('table2') 

this approach keeps you model leaner and does not require additional work while refreshing.

 

Richard

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks for the reply. While this works well for grid and chart values, a measure can't be used in slicers and filters. Something that I didn't specify in my post is that I need to be able to filter records where count>X. Otherwise, I like this solution for other applications. Thanks!