Search code examples
daxpowerpivot

Count number times an observation appears connected to another column. DAX

I'm working in a data set that tracks ownership of firms in a database. I have three databases. One with all the firms, one with all unique owners and one database with the relations between the owners and the firms. Each owner can own multiple firms. I want a calculated column that for each firm shows how many firms the specific owner or owners has. I have no idea how to do it. It is something like; for each firm, search how many times the owner or owners appears in the relationship database and sum those to give a number for each firm.

Solution

  • After all data is in data model and relationships are created, calculated column in table Firms should look like this:

    CountFirmsOfOwner = COUNTROWS(FILTER(Firms, Firms[OwnerID] = EARLIER(Firms[OwnerID])))