Counting IDs in a another table by a common key

[rischav]

Need a quick help! Spent my whole evening finding the following:

I have two tables, G(Accounts, REF 1) and F(Super Account, Accounts). They both have a common key Accounts. At the moment in G table I'm counting the number Ref IDs by Account. So account 10 has 4 Ref IDs, 11 has 2 and 23 has 5.

Now, in table F, accounts is a subset of Super account.. When I drag Accounts from table F and count of Ref ID from Table G in a table visual, I get the total counts of Ref IDs that is 11 for all rows and not the count for each account.

My ultimate need is to count ref ids by super accounts that comprises of the accounts. Need help

[Anonymous]

For 2 tables to be filtered at the same time, you have to create a relationship between them (direct or indirect). You should have 2 tables: Accounts with only unique accounts and Super Accounts with only unique super accounts. Your G and F tables would then be fact tables (hidden from view). This way you'd connect your dimensions to your fact tables on the relevant fields. Then, you'd create relevant measures. They would be easy to create as the structure I described already does the heavy lifting.

[CNENFRNL · Accepted solution]

At least 2 ways to solve your issue,

1. Create relationship between F and G with Accounts column as key
2. Without physical relationship, use TREATAS func to establish virtual relationship for your measure

[rischav]

Thank you so much for taking the time to reply! It worked!