Cross Selling List

[DAXNewbie2023]

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

[Ashish_Mathur]

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.

[Ashish_Mathur]

Hi,

Share a larger/representative table with more client sub-types.  On that revised table, show the expected result.

[DAXNewbie2023]

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 

[v-yiruan-msft]

Hi @DAXNewbie2023 ,

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

[DAXNewbie2023]

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

[v-yiruan-msft]

Hi @DAXNewbie2023 ,

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

[DAXNewbie2023]

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 )

RETURN

    CONCATENATEX (

        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