Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AH2022
Helper II
Helper II

Intersect Table returns list

Hi All,

For this sample data : 

AH2022_0-1661304999755.png

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.

AH2022_1-1661305064952.png

 

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 :

AH2022_2-1661305064954.png

 

Expected result

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

 

Thank you!

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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] ,

       ", "

     

    )

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.