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 ,

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