topic Intersect Table returns list in DAX Commands and Tips

Intersect Table returns list

AH2022 — Wed, 24 Aug 2022 01:40:25 GMT

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 ( _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!

Re: Intersect Table returns list

amitchandak — Wed, 24 Aug 2022 03:57:11 GMT

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-Retention/ba-p/1377458

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

Re: Intersect Table returns list

AH2022 — Wed, 24 Aug 2022 21:11:29 GMT

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

", "

)