DAX: Filter on a filter with countrows

Question

I'm working in a dataset (relations) that tracks ownership of firms. If a firm has 8 owners, the firm has 8 rows in the dataset, one for each owner. A owner can have multiple firms. I want a calculated column that for row shows how many firms the specific owner owns in that sector. It is something like; for each row, search how many times the owner or owners appears in the relationship database but only count those that has the same industry code like the mother firm. This is what i have so far:

=
CALCULATE (
    COUNTROWS ( Relations );
    FILTER (
        Relations;
        Relations[participantnumber] = EARLIER ( Relations[participantnumber] )
    );
    FILTER ( Relations; Relations[127_industry] = Relations[127_industry] )
)

But this just gives me the total amount of firms an owner is mentioned in regardless of industry code. Thanks!

RADO RADO · Accepted Answer · 2019-01-31T14:48:47

Try this:

=
CALCULATE (
    COUNTROWS ( Relations );
    ALLEXCEPT( Relations; Relations[participantnumber]; Relations[127_industry])
    )

How it works: for each row, you need to have access to the entire table so that you can count all relevant relations. However, you want to filter the total count by participant and industry that are current for the row. ALLEXCEPT does that - it allows you see the entire table while preserving current participant and industry.

DAX: Filter on a filter with countrows

1 Answers