Thread: Intersect - except - calculatetable

1. Intersect - except - calculatetable

Hi All,

I am struggling with a calculation for number of exam candidates who became members in a particular month, I have the below:

Joiners:=
COUNTROWS (
INTERSECT (
VALUES ( Exams[Contact Number] ),
EXCEPT (
VALUES ( Members[Contact Number] ),
CALCULATETABLE (
VALUES ( Members[Contact Number] ),
Members[Status] = "Current/Active",
FILTER( ALL( Members ), Members[Financial Period] < MIN ( Calendar[Financial Period] ) )
)
)
)
)

The logic is - Everyone who sat this exam AND is active in this month BUT was not active in any previous month.

I'm stuck on the bold line, the table of members active in previous months. Does anyone have any suggestions?

Thanks
Rich

2. Can you post the workbook for us to work with?

3. Hi,

Here is a much chopped down version. I have removed the Current/Active column to keep things simple.

The Result page shows the structure of my desired output.

Thanks
Rich

4. ok nailed it, had to bone up on filter context etc and sit in a dark room and think for a while
Code:
```Exam Joiners =
COUNTROWS (
INTERSECT (
VALUES ( Exams[Contact Number] ),
EXCEPT (
CALCULATETABLE (
VALUES ( 'Member Data'[Contact Number] ),
'Member Data'[Status] = "Current/Active"
),
CALCULATETABLE (
VALUES ( 'Member Data'[Contact Number] ),
FILTER (
ALL ( 'Member Data' ),
AND (
'Member Data'[Financial Period] < MIN ( Calendar[Financial Period] ),
'Member Data'[Status] = "Current/Active"
)
)
)
)
)
)```

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•