Calculate measure where sold user = user from related table?

[jd8766]

Hi, I have a sales table and company table like the below.. I am trying to build a measure that counts the number of contractkeys per user... but only where the solduserkey is the same owneruserkey in the company table.

So for example, for contractkey 10, the solduserkey was 111 and the companykey was 736. On the company table, companykey 736 is "owned" by 111, so we would count this sale.

ContractKey 12 has a sold user key of 112 and a companykey of 222... but on the company table, companykey 222 is owned by owner user key 111 so this wouldn't count.

Does that make sense? What would the best way be to achieve this? (using SSAS tabular live connection)

[v-jingzhang · Accepted solution]

Hi @jd8766 

You can try this measure

Count of Contracts =
VAR _companyKeys =
    CALCULATETABLE (
        VALUES ( Company[CompanyKey] ),
        FILTER ( Company, Company[OwnerUserKey] IN VALUES ( Sales[SoldUserKey] ) )
    )
RETURN
    COUNTX (
        FILTER ( Sales, Sales[CompanyKey] IN _companyKeys ),
        Sales[ContractKey]
    ) + 0

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.