Forum Discussion

mrawat7's avatar
mrawat7
Frequent Visitor
2 years ago
Solved

Count records in table but not in same table with filter

I'm trying replicate a SQL query i have in Power BI.

 

The query gives me a count of customers, that were not members the previous year.

 

I have a table of Memberships with the following columns:

MembershipID (PK), CustomerID, StartDate, ExpiryDate.

 

A customer can have many membership records but with differing Start and Expiry Dates.

 

Typically, a customer will have a membership record for each year in membership.

 

In SQL, I use the following query:

 

Select distinct count(CustomerID)

from Memberships

where StartDate between @varStartEnd and @varEndDate

and ContactID not in

    (select CustomerID from Membership where StartDate between dateadd(year, -1,@varStartEnd) and dateadd(year, -1, @varEndDate)

 

I'm trying to achieve the same in Power BI.

 

I already have a Measure with filters for Start Date and Expiry Date that gives me the count of distinct CustomerIDs between dates selected:

 

Members between Dates = 
CALCULATE (
    DISTINCTCOUNT ( 'Memberships'[CustomerID] ),
    FILTER (
        'Memberships',
        'Memberships'[StartDate] >= MIN ( 'Start Date'[Start Date] )
            && 'Memberships'[StartDate] <= MAX ( 'End Date'[End Date] )
    )
)

 

 

But I'm stuck on the second part of the query to exclude CustomerIDs that have related membership records the previous year.

 

Hope that makes sense!

 

Need Help
  • amustafa's avatar
    amustafa
    2 years ago

    Sorry about that. Try this 

     

    Members between Dates = 

    VAR StartDateRange = MIN('Start Date'[Start Date])

    VAR EndDateRange = MAX('End Date'[End Date])

    VAR PreviousYearMembers = CALCULATETABLE (

        VALUES('Memberships'[CustomerID]),

        FILTER (

            ALL('Memberships'),

            'Memberships'[StartDate] >= DATEADD(StartDateRange, -1, YEAR)

                && 'Memberships'[StartDate] <= DATEADD(EndDateRange, -1, YEAR)

        )

    )

    VAR CurrentYearMembers = CALCULATETABLE (

        VALUES('Memberships'[CustomerID]),

        FILTER (

            ALL('Memberships'),

            'Memberships'[StartDate] >= StartDateRange

                && 'Memberships'[StartDate] <= EndDateRange

        )

    )

    RETURN

    COUNTROWS(

        EXCEPT(CurrentYearMembers, PreviousYearMembers)

    )

  • mrawat7's avatar
    mrawat7
    2 years ago

    I managed to adapt your solution to figure it out.

     

    There may be a better way to do it but this is what i did:

     

    New Members between Dates LY 2 =

    VAR StartDateRange = MIN('Start Date'[Start Date])

    VAR EndDateRange = MAX('End Date'[End Date])

    VAR StartDateRangeLY = MIN('Start Date'[Date LY])

    VAR EndDateRangeLY = MAX('End Date'[End Date LY])

    VAR PreviousYearMembers = CALCULATETABLE (

        VALUES('Membership'[CustomerID]),

        FILTER (

            ALL('Membership'),

            'Membership'[StartDate] >= StartDateRangeLY

                && 'Membership'[StartDate] <= EndDateRangeLY

        )

    )

    VAR CurrentYearMembers = CALCULATETABLE (

        VALUES('Membership'[CustomerID]),

        FILTER (

            ALL('Membership'),

            'Membership'[StartDate] >= StartDateRange

                && 'Membership'[StartDate] <= EndDateRange

        )

    )

    VAR NewMembers = CALCULATETABLE(

        EXCEPT(CurrentYearMembers, PreviousYearMembers)

    )

    RETURN
    CALCULATE(
         distinctcount(Membership[CustomerID]),(Membership[CustomerID] in NewMembers)
    )

5 Replies

  • amustafa's avatar
    amustafa
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Here's how you can update your DAX to replicate excluding customers who were members in the previous year.

     

    Members between Dates = 
CALCULATE (
    DISTINCTCOUNT ( 'Memberships'[CustomerID] ),
    FILTER (
        'Memberships',
        'Memberships'[StartDate] >= MIN ( 'Start Date'[Start Date] )
            && 'Memberships'[StartDate] <= MAX ( 'End Date'[End Date] )
    ),
    NOT (
        CALCULATETABLE (
            'Memberships',
            FILTER (
                ALL ( 'Memberships' ),
                'Memberships'[StartDate] >= DATEADD ( MIN ( 'Start Date'[Start Date] ), -1, YEAR )
                    && 'Memberships'[StartDate] <= DATEADD ( MAX ( 'End Date'[End Date] ), -1, YEAR )
            )
        )
    )
)

     

  • mrawat7's avatar
    mrawat7
    Frequent Visitor
    2 years ago

    Hi,

     

    Thanks for responding.  Unfortunately, I get the following error:

    The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

    • amustafa's avatar
      amustafa
      Icon for Solution Sage rankSolution Sage
      2 years ago

      Sorry about that. Try this 

       

      Members between Dates = 

      VAR StartDateRange = MIN('Start Date'[Start Date])

      VAR EndDateRange = MAX('End Date'[End Date])

      VAR PreviousYearMembers = CALCULATETABLE (

          VALUES('Memberships'[CustomerID]),

          FILTER (

              ALL('Memberships'),

              'Memberships'[StartDate] >= DATEADD(StartDateRange, -1, YEAR)

                  && 'Memberships'[StartDate] <= DATEADD(EndDateRange, -1, YEAR)

          )

      )

      VAR CurrentYearMembers = CALCULATETABLE (

          VALUES('Memberships'[CustomerID]),

          FILTER (

              ALL('Memberships'),

              'Memberships'[StartDate] >= StartDateRange

                  && 'Memberships'[StartDate] <= EndDateRange

          )

      )

      RETURN

      COUNTROWS(

          EXCEPT(CurrentYearMembers, PreviousYearMembers)

      )

      • mrawat7's avatar
        mrawat7
        Frequent Visitor
        2 years ago

        I managed to adapt your solution to figure it out.

         

        There may be a better way to do it but this is what i did:

         

        New Members between Dates LY 2 =

        VAR StartDateRange = MIN('Start Date'[Start Date])

        VAR EndDateRange = MAX('End Date'[End Date])

        VAR StartDateRangeLY = MIN('Start Date'[Date LY])

        VAR EndDateRangeLY = MAX('End Date'[End Date LY])

        VAR PreviousYearMembers = CALCULATETABLE (

            VALUES('Membership'[CustomerID]),

            FILTER (

                ALL('Membership'),

                'Membership'[StartDate] >= StartDateRangeLY

                    && 'Membership'[StartDate] <= EndDateRangeLY

            )

        )

        VAR CurrentYearMembers = CALCULATETABLE (

            VALUES('Membership'[CustomerID]),

            FILTER (

                ALL('Membership'),

                'Membership'[StartDate] >= StartDateRange

                    && 'Membership'[StartDate] <= EndDateRange

            )

        )

        VAR NewMembers = CALCULATETABLE(

            EXCEPT(CurrentYearMembers, PreviousYearMembers)

        )

        RETURN
        CALCULATE(
             distinctcount(Membership[CustomerID]),(Membership[CustomerID] in NewMembers)
        )