Forum Discussion
Cross Selling List
Hi Anonymous ,
You can create a measure as below to get it, please find the details in the attachment.
Measure =
CONCATENATEX (
VALUES ( 'Table'[Investment Strategy Name] ),
'Table'[Investment Strategy Name],
UNICHAR ( 10 ),
'Table'[Investment Strategy Name]
)
Best Regards
Hi - I have already done this. Let me explain the desired outcome in more descriptive manner. I have attached a screenshot below of what I am trying to do here. Thank you!
Step 1 - User will select the desired client sub-type from slicer 1
Step 2 - Based on the selected slicer 1, Table 1 will show all the strategies of the client where the sub-type has invested in. Based on the slicer 1 selection the slicer 2 will update to show the list of clients in that chosen sub-type
Step 3 - In Slicer 2, the user will select the desired client name and the Table 2 will update to show the strategies of the desired client
Step 4 - In Table 3, I want to show the strategies where the chosen client has not invested in, but the peer groups (i.e. the other clients in the same sub-type) have invested in.
Hope this makes sense now. Please feel free to reply here if not, and I can share the PBI and Excel file with this data. Thanks a lot for the help!
Strategy Client/Third Party Name Client Sub-Type Strategy 1 Client A National Strategy 2 Client Q Sovereign Wealth Fund Strategy 3 Client Q Sovereign Wealth Fund Strategy 4 Client A National Strategy 5 Client A National Strategy 6 Client Q Sovereign Wealth Fund Strategy 7 Client A National Strategy 6 Client P Retail Bank Strategy 8 Client P Retail Bank Strategy 9 Client O Retail Bank Strategy 10 Client O Retail Bank Strategy 11 Client O Retail Bank Strategy 8 Client J Retail Bank Strategy 7 Client P Retail Bank Strategy 12 Client P Retail Bank Strategy 13 Client P Retail Bank Strategy 14 Client R Sovereign Wealth Fund Strategy 15 Client O Retail Bank Strategy 16 Client P Retail Bank Strategy 17 Client O Retail Bank Strategy 18 Client O Retail Bank Strategy 19 Client L Retail Bank Strategy 7 Client K Retail Bank Strategy 20 Client H Retail Bank Strategy 21 Client N Retail Bank Strategy 22 Client L Retail Bank Strategy 23 Client L Retail Bank Strategy 12 Client E Retail Bank Strategy 24 Client K Retail Bank Strategy 25 Client L Retail Bank Strategy 23 Client F Retail Bank Strategy 3 Client H Retail Bank Strategy 19 Client H Retail Bank Strategy 26 Client F Retail Bank Strategy 27 Client L Retail Bank Strategy 28 Client L Retail Bank Strategy 19 Client K Retail Bank Strategy 29 Client I Retail Bank Strategy 30 Client I Retail Bank Strategy 31 Client H Retail Bank Strategy 32 Client H Retail Bank Strategy 33 Client H Retail Bank Strategy 34 Client M Retail Bank Strategy 35 Client B National Strategy 36 Client C National Strategy 37 Client D National Strategy 38 Client S Sovereign Wealth Fund Strategy 34 Client G Retail Bank Strategy 39 Client O Retail Bank Strategy 37 Client M Retail Bank Strategy 40 Client M Retail Bank Strategy 41 Client G Retail Bank Strategy 42 Client N Retail Bank Strategy 43 Client P Retail Bank Strategy 1 Client E Retail Bank Strategy 44 Client H Retail Bank Strategy 45 Client F Retail Bank Strategy 32 Client N Retail Bank Strategy 30 Client N Retail Bank Strategy 12 Client H Retail Bank Strategy 3 Client K Retail Bank Strategy 46 Client H Retail Bank Strategy 47 Client I Retail Bank Strategy 25 Client I Retail Bank Strategy 48 Client H Retail Bank Strategy 48 Client K Retail Bank Strategy 49 Client H Retail Bank Strategy 46 Client K Retail Bank Strategy 44 Client J Retail Bank Strategy 50 Client J Retail Bank Strategy 51 Client J Retail Bank Hi Anonymous ,
I updated my sample pbix file(see the attachment) base on your provided info, please check if it can return your expected result...
Not invested strategies = VAR _allstra = CALCULATETABLE ( VALUES ( 'Table'[Strategy] ), ALL ( 'Table' ) ) VAR _selstra = CALCULATETABLE ( VALUES ( 'Table'[Strategy] ), ALLSELECTED ( 'Table' ) ) VAR _list = EXCEPT ( _allstra, _selstra ) RETURN CONCATENATEX ( FILTER ( ALL ( 'Table'[Strategy] ), 'Table'[Strategy] IN _list ), 'Table'[Strategy], "," )Best Regards
Hi - Thank you for this. We are almost there, but, not quite there yet. This has worked, but only to a certain extent where in it's taking all of the strategies. But, I want the Table 3 to take only the strategies where the other clients in the client sub-type have invested in (The values from Table 1) and 'not all' strategies.
I have used the DAX measure provided by you and got the output in Table 3 which includes all the strategies from all client sub-types. But, I want the Table 3 to show only the 'Table 1 Strategies' less the 'Table 2 Strategies'. In this case, I want it to show only 'Strategy 1, 4, 5, 7, 35, 36' only. Please refer to the last screenshot for better understanding of what I mean here.
I am trying to attach the pbix file in which I integrated your measure, but it's not uploading. Here are the additional DAX measures I created for showing the Table 1 and Table 2 values.
Thanks a lot for all the help! 😄
Additional info
1. Strategies of the selected client = CONCATENATEX(VALUES(Sheet1[Strategy]), Sheet1[Strategy], ", ")2. Strategies of selected client sub-type = CONCATENATEX(VALUES(Sheet1[Strategy]), Sheet1[Strategy], ", ")3. Cross Selling Opportunity Strategies =VAR _allstra =CALCULATETABLE ( VALUES ( Sheet1[Strategy] ), ALL ( Sheet1 ) )VAR _selstra =CALCULATETABLE ( VALUES ( Sheet1[Strategy] ), ALLSELECTED ( Sheet1 ) )VAR _list =EXCEPT ( _allstra, _selstra )RETURNCONCATENATEX (FILTER ( ALL ( Sheet1[Strategy] ), Sheet1[Strategy] IN _list ),Sheet1[Strategy],",")Bullet #3 measure is nothing but what you have provided
All the columns in the data set with custom measures created
Slicer 1 interactions - It only interacts with Table 1 and Slicer 2
Slicer 2 interactions - It only interacts with Table 2 and Table 3
Output from the Table 3 based on the measure that you have suggested earlier