Forum Discussion

kroman's avatar
kroman
Icon for Helper II rankHelper II
4 years ago
Solved

Custom column that counts rows from other table

Hi All

I have 2 tables: AllFiles (Url (unique) )  and  FilesAccessed (Url, AccessDate)
I need to add custom column to AllFiles that would show how many times particular file was accessed. So something like "NewColumn = CountRows(Filter( FilesAccessed WHERE FilesAccessed[Url] = this row AllFiles[Url] ) 

But just cannot figure out how to do it. 

Thanks

 

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi kroman 

    You can create a new calculated column for AllFiles and calculate the number of URLs that coincide with FilesAccessed. See if you can solve your problem.

    Column =
CALCULATE (
    DISTINCTCOUNT ( FilesAccessed[URL] ),
    FILTER ( 'FilesAccessed', 'FilesAccessed'[URL] = EARLIER ( 'All Files'[URL] ) )
)

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

    Best Regards

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

     

8 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi kroman 

     

    try this code to add a new column:

     

     

     

    NewColumn =
CALCULATE (
    COUNTROWS ( FilesAccessed ),
    FILTER ( FilesAccessed , FilesAccessed [Url] = EARLIER ( AllFiles[Url] ) )
)

     

     




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

    Appreciate your Kudos!!

     

    • kroman's avatar
      kroman
      Icon for Helper II rankHelper II
      4 years ago

      Getting "The name 'CALCULATE' wasn't recognized. Make sure it's spelled correctly."

  • kroman's avatar
    kroman
    Icon for Helper II rankHelper II
    4 years ago

    Done it via "Modeling" > "New Column" but getting blank fileds, although there is some values in Search

    • VahidDM's avatar
      VahidDM
      Icon for Super User rankSuper User
      4 years ago

      kroman 

       

      Have you checked those Url that have blank? is there any value in FilesAccessed table for those Url?

       

      Try this if there is Url for them:

      NewColumn =
CALCULATE (
    COUNTROWS ( FilesAccessed ),
    FILTER ( all(FilesAccessed) , FilesAccessed [Url] = AllFiles[Url] )
)

       

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

      Appreciate your Kudos!!

       

      • kroman's avatar
        kroman
        Icon for Helper II rankHelper II
        4 years ago

        Tried it - same result 😕 
        Checked values in Url columns in both tables - values are there 

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi kroman 

    You can create a new calculated column for AllFiles and calculate the number of URLs that coincide with FilesAccessed. See if you can solve your problem.

    Column =
CALCULATE (
    DISTINCTCOUNT ( FilesAccessed[URL] ),
    FILTER ( 'FilesAccessed', 'FilesAccessed'[URL] = EARLIER ( 'All Files'[URL] ) )
)

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

    Best Regards

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