Results 1 to 4 of 4

Thread: Intersect - except - calculatetable

  1. #1
    Acolyte DickyMoo's Avatar
    Join Date
    Mar 2016
    Location
    London
    Posts
    27
    Articles
    0

    Intersect - except - calculatetable



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,621
    Articles
    0
    Excel Version
    O365
    Can you post the workbook for us to work with?

  3. #3
    Acolyte DickyMoo's Avatar
    Join Date
    Mar 2016
    Location
    London
    Posts
    27
    Articles
    0
    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
    Attached Files Attached Files

  4. #4
    Acolyte DickyMoo's Avatar
    Join Date
    Mar 2016
    Location
    London
    Posts
    27
    Articles
    0
    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
  •