COUNTROWS matching value on a column from an unrelated table

[olimilo]

The tables cannot be related due to a M:M relationship (the duplicates are apparently valid). We just need to create a calculated column that counts the matches from an unrelated table. TL table is to show the count of the matching TLID rows from the PM table, irrespective of the TLKey or PMKey:

Intended output:

TLKey		TLID		Matches
TLKey_001		TL012230		2
TLKey_002		TL041908		2
TLKey_003		TL012230		2
TLKey_004		TL041908		2

PMKey		TLID
PMKey_001		TL012230
PMKey_002		TL041908
PMKey_003		TL012230
PMKey_004		TL041908

I tried the following calculated column but it is turning an error due to the missing relationship:

Matches = 
    COUNTROWS(
        FILTER(
            'T2',
            'T2'[TL ID] = 'T1'[TL_ID])
        )
    )

[Ashish_Mathur]

Hi,

Write this calculated column formula in the TL table

Matches = calculate(countrows(PM),filter(PM,PM[TLID]=earlier(TL[TLID])))

Hope this helps.

[Anonymous]

Hi @olimilo 

Your solution is great, @Ritaf1983 . Here are my additions.

I had no relationship between my two tables and then I used your method to get the correct result.

Best Regards,
Yulia Xu

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

[Ritaf1983 · Accepted solution]

Hi @olimilo 
Add "calculate" at will fix the issue.

count_repeats = CALCULATE(COUNTROWS(T2),FILTER('T2',T1[test id]='T2'[ID 2]))

pbix with example is attached

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