Forum Discussion
Intersect Table returns list
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!
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
2 Replies
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
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] ,
", "
)
