PDA

View Full Version : Intersect - except - calculatetable

DickyMoo
2017-07-22, 11:03 PM
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

Bob Phillips
2017-07-23, 12:49 PM
Can you post the workbook for us to work with?

DickyMoo
2017-07-24, 11:26 AM
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

DickyMoo
2017-07-24, 10:26 PM
ok nailed it, had to bone up on filter context etc and sit in a dark room and think for a while

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"
)
)
)
)
)
)