Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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/pee...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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!

 

 

 

StrategyClient/Third Party NameClient Sub-Type
Strategy 1Client ANational
Strategy 2Client QSovereign Wealth Fund
Strategy 3Client QSovereign Wealth Fund
Strategy 4Client ANational
Strategy 5Client ANational
Strategy 6Client QSovereign Wealth Fund
Strategy 7Client ANational
Strategy 6Client PRetail Bank
Strategy 8Client PRetail Bank
Strategy 9Client ORetail Bank
Strategy 10Client ORetail Bank
Strategy 11Client ORetail Bank
Strategy 8Client JRetail Bank
Strategy 7Client PRetail Bank
Strategy 12Client PRetail Bank
Strategy 13Client PRetail Bank
Strategy 14Client RSovereign Wealth Fund
Strategy 15Client ORetail Bank
Strategy 16Client PRetail Bank
Strategy 17Client ORetail Bank
Strategy 18Client ORetail Bank
Strategy 19Client LRetail Bank
Strategy 7Client KRetail Bank
Strategy 20Client HRetail Bank
Strategy 21Client NRetail Bank
Strategy 22Client LRetail Bank
Strategy 23Client LRetail Bank
Strategy 12Client ERetail Bank
Strategy 24Client KRetail Bank
Strategy 25Client LRetail Bank
Strategy 23Client FRetail Bank
Strategy 3Client HRetail Bank
Strategy 19Client HRetail Bank
Strategy 26Client FRetail Bank
Strategy 27Client LRetail Bank
Strategy 28Client LRetail Bank
Strategy 19Client KRetail Bank
Strategy 29Client IRetail Bank
Strategy 30Client IRetail Bank
Strategy 31Client HRetail Bank
Strategy 32Client HRetail Bank
Strategy 33Client HRetail Bank
Strategy 34Client MRetail Bank
Strategy 35Client BNational
Strategy 36Client CNational
Strategy 37Client DNational
Strategy 38Client SSovereign Wealth Fund
Strategy 34Client GRetail Bank
Strategy 39Client ORetail Bank
Strategy 37Client MRetail Bank
Strategy 40Client MRetail Bank
Strategy 41Client GRetail Bank
Strategy 42Client NRetail Bank
Strategy 43Client PRetail Bank
Strategy 1Client ERetail Bank
Strategy 44Client HRetail Bank
Strategy 45Client FRetail Bank
Strategy 32Client NRetail Bank
Strategy 30Client NRetail Bank
Strategy 12Client HRetail Bank
Strategy 3Client KRetail Bank
Strategy 46Client HRetail Bank
Strategy 47Client IRetail Bank
Strategy 25Client IRetail Bank
Strategy 48Client HRetail Bank
Strategy 48Client KRetail Bank
Strategy 49Client HRetail Bank
Strategy 46Client KRetail Bank
Strategy 44Client JRetail Bank
Strategy 50Client JRetail Bank
Strategy 51Client JRetail Bank

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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