Custom column that counts rows from other table

[kroman]

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

[v-zhangti · Accepted solution]

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

[kroman]

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

[VahidDM]

@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]

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

[VahidDM]

Try this then:

New Column =
COUNTROWS (
    FILTER ( ALL ( 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]

Still the same 😕

[VahidDM]

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]

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