Forum Discussion
Cross Selling List
Hi All - I have a table like below. Now, I want to see a list of all strategies in a visualization like a table (using DAX measures or calculated columns or logics) where the same client sub-type/peers has invested in, but not the client under discussion. Like the ‘Central Bank’ client sub-type, we will be having multiple sub-type. But one client can only be only one sub-type at any given instance. And, one client can have multiple strategies.
A measure would be great. All the columns are in the same table.
Investment Strategy Name | Client Name | Client Sub-Type
|
Strategy 1 | Client A | Central Bank |
Strategy 1 | Client B | Central Bank |
Strategy 3 | Client B | Central Bank |
Strategy 1 | Client A | Central Bank |
Strategy 4 | Client C | Central Bank |
Strategy 5 | Client A | Central Bank |
Strategy 1 | Client D | Central Bank |
Strategy 2 | Client D | Central Bank |
Strategy 1 | Client E | Central Bank |
Strategy 9 | Client D | Central Bank |
Strategy 8 | Client D | Central Bank |
7 Replies
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,
Share a larger/representative table with more client sub-types. On that revised table, show the expected result.
Hi Mathur - Thanks for responding. Please find below the broader data set. I have attached a screenshot below of what I am trying to do here.
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 I have
Hi,
I have not been able to get all "not invested in" strategies in a card visual but i have been able to geenrate a list in a Table.
Hope this helps.
