PowerBi Dax subquery with filter

Question

any help on this is greatly appreciated.

I'm looking to count the rows in a table where the PersonID is in a subquery list with a filter condition. In SQL it would look like this.

select count(*)
from tableA
where PersonId in(select distinct PersonId from tableA where CallResult = 1)

tableA has the same PersonId multiple times for each day and I'm looking to count how many times that PersonId is in the table but only if the PersonId has CallResult = 1 in any row within the table. There are other PersonId that don't have CallResult = 1 and I'm not looking to count those.

Maybe I'm overthinking this one but dax isn't my strength

PersonId | CallResult | CallNumber |
AB12          1             3
AB12          0             2
AB12          0             1
CD21          0             2
CD21          0             1
EF32          1             2
EF32          0             1

In this example I would expect the subquery to return AB12 and EF32 and the count to be 5 (3+2) calls

Alessandro Alessandro · Accepted Answer · 2020-07-21T20:41:22

I may have figured it out. Posting here in case it helps anyone.

Count = CALCULATE(
    SUM(TableA[Calls]),
    CALCULATETABLE(
        SUMMARIZE(TableA,TablA[PersonID]),
        TableA[CallResult]=1
    )
)

PowerBi Dax subquery with filter

1 Answers