Intersect Table returns list

[AH2022]

Hi All,

For this sample data : 

there is a measure returning the list of clients who purchased 3 last consecutive years,

(19-20, 20-21, 21-22) and NOT 22-23.

1Customers list 3yRow not TY =

VAR _currentyear =

    YEAR ( TODAY () )

VAR _oneyearago = _currentyear - 1

VAR _twoyearsago = _currentyear - 2

VAR _threeyearsago = _currentyear - 3

VAR _currentyearnosalescustomers =

    SUMMARIZE (

        FILTER (

            CROSSJOIN ( VALUES ( 'Calendar'[Year] ), VALUES ( Customer[Customer] ) ),

            'Calendar'[Year] = _currentyear

                && [Total Sales] = BLANK ()

        ),

        Customer[Customer]

    )

VAR _oneyearagocustomerslist =

    SUMMARIZE (

        FILTER (

            CROSSJOIN ( VALUES ( 'Calendar'[Year] ), VALUES ( Customer[Customer] ) ),

            'Calendar'[Year] = _oneyearago

                && [Total Sales] <> BLANK ()

        ),

        Customer[Customer]

    )

VAR _twoyearagocustomerslist =

    SUMMARIZE (

        FILTER (

            CROSSJOIN ( VALUES ( 'Calendar'[Year] ), VALUES ( Customer[Customer] ) ),

            'Calendar'[Year] = _twoyearsago

                && [Total Sales] <> BLANK ()

        ),

        Customer[Customer]

    )

VAR _threeyearagocustomerslist =

    SUMMARIZE (

        FILTER (

            CROSSJOIN ( VALUES ( 'Calendar'[Year] ), VALUES ( Customer[Customer] ) ),

            'Calendar'[Year] = _threeyearsago

                && [Total Sales] <> BLANK ()

        ),

        Customer[Customer]

    )

RETURN

    CONCATENATEX (

        INTERSECT (

            INTERSECT (

                INTERSECT ( _currentyearnosalescustomers, _oneyearagocustomerslist ),

                _twoyearagocustomerslist

            ),

            _threeyearagocustomerslist

        ),

        Customer[Customer],

        ", "

    )

The code works, I get the list with the ID of customers.

The requirement is to display ID & Name of customer.

If I Return :

Customer[Customer], it works

If I return

Customer [Customer] &"-"& Customer [ Name]  

I get this error : Column 'Name' cannot be found or may not be used in this expression. The column is in the same table.

Customer [Customer] is a ID, numerical

Customer [Name]  is text

1 ID can only have 1 name.

Result :

Expected result

Co1 – Name, C03-Name, C13 – Name, etc

Thank you!

[amitchandak · Accepted solution]

@AH2022 , the approach will not have customer name .

I am assume you have [Purchased in the Last three years] and [purchased this year]

then using the customer table 

Concatenatex(filter(Customer,not(isblank([Purchased in the Last three years])) && isblank([purchased this year] ) ) , Customer [Customer] &"-"& Customer [ Name] , " , " )

The same approach I followed here.

Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

But use customer table, not values(Customer[Customer])

Approch used in this video -Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

[AH2022]

I found also the solution with the Intersect code.

Name of Customers list 1yAgo not TY =

VAR _currentyear =

    YEAR ( TODAY () )

VAR _oneyearago = _currentyear - 1

VAR _currentyearnosalescustomers =

    SUMMARIZE (

        FILTER (

            CROSSJOIN ( VALUES ( Calendar[Year] ), VALUES ( Customer[Customer] ) ), VALUES ( Customer[Name] ) ),

            Calendar[Year] = _currentyear

                && [Total Sales] = BLANK ()

        ),

        Customer[Customer], Customer[Name]

    )

VAR _oneyearagocustomerslist =

    SUMMARIZE (

        FILTER (

            CROSSJOIN ( VALUES ( Calendar[Year] ), VALUES ( Customer[Customer] ) , VALUES ( Customer[Name] )),

            Calendar[Year] = _oneyearago

                && [Total Sales] <> BLANK ()

        ),

        Customer[Customer],  Customer[Name]

    )

RETURN

    CONCATENATEX (

                INTERSECT ( _currentyearnosalescustomers, _oneyearagocustomerslist ),

     UNICHAR(10) &   [Customer] &" , " & [Name] ,

       ", "

    )