Finding Common attributes between two different Datasets

[Anonymous]

I have two data sets Group A and Group B as shown below. 

GroupA	Breakfast	Lunch	Dinner		GroupB	Breakfast	Lunch	Dinner
A1	Cereal	Pasta	Vegetables		B1	Cereal	Pasta	Vegetables
A2	Eggs	Fast food	Meat		B2	Fruit	Fast food	Bread
A3	Fruit	Rice	Bread		B3	Cereal	Fast food	Meat
A4	Cereal	Sandwich	Meat		B4	Eggs	Sandwich	Bread

I would like to be able to define a relationship between the members of group A and group B. For example, If I pick a member from Group A, say A1, I would like to know how similar it is to each member of Group B. In this case A1 is exactly like B1 as all three categories match - in this case relationship strength is 3. But A1 is very different from B4 where relationship strength is 0. So I would like to have 1) the strength of the relationship (0 rto 3) and 2) details of the relationship - ie which fields are common. 

THank you in advance for your help Community. 

[TomMartens · Accepted solution]

Hey @Anonymous ,

I created two tables Table A and Table B

I creatd 2 measures (looking for A in B), just create additional measures for the other way round.

These are my measures:

level of similarity = 
IF( HASONEVALUE( 'TableA'[GroupA] )

    , IF( HASONEVALUE( 'TableB'[GroupB] )
        , var searchstring = 
            CONCATENATEX(
                'TableA'
                , COMBINEVALUES( "|", 'TableA'[Breakfast] , 'TableA'[Lunch] , 'TableA'[Dinner] )
                , "|"
            )
        return
            SUMX(
                'TableB'
                , var stringToCompare = COMBINEVALUES( "|", 'TableB'[Breakfast] , 'TableB'[Lunch] , 'TableB'[Dinner] )
                var lengthSearch = PATHLENGTH( searchstring )
                var t =
                    ADDCOLUMNS( 
                        GENERATESERIES( 1 , lengthSearch , 1 )
                        , "isfound" , IF( PATHCONTAINS( stringToCompare , PATHITEM( searchstring , [Value] ) ) , 1 , 0 )
                    )
                return
                SUMX( t , [isfound] )
            )
        , BLANK()
    )
, "Select one item from Group A"
)

and

similar items = 
IF( HASONEVALUE( 'TableA'[GroupA] )
    , IF( HASONEVALUE( 'TableB'[GroupB] )
        , var searchstring = 
            CONCATENATEX(
                'TableA'
                , COMBINEVALUES( "|", 'TableA'[Breakfast] , 'TableA'[Lunch] , 'TableA'[Dinner] )
                , "|"
            )
        return
            CONCATENATEX(
                'TableB'
                , var stringToCompare = COMBINEVALUES( "|", 'TableB'[Breakfast] , 'TableB'[Lunch] , 'TableB'[Dinner] )
                var lengthSearch = PATHLENGTH( searchstring )
                var t =
                    ADDCOLUMNS( 
                        GENERATESERIES( 1 , lengthSearch , 1 )
                        , "founditem" , IF( PATHCONTAINS( stringToCompare , PATHITEM( searchstring , [Value] ) ) , PATHITEM( searchstring , [Value] ) , BLANK() )
                    )
                return
                CONCATENATEX( t , [founditem] , ", " )
            )
        , BLANK()
    )
, "Select one item from Group A"
)

This allows to create this report:

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom

[amitchandak]

@Anonymous , You can create a table like

A  B

A1 B1

A2 B2

Join with table 1 on GroupA and table 2 Groupb and then you can view or filter(slicer) then together

[Anonymous]

Thanks Amit. I expect that both my tables are going to have 1000s of rows. and I would like to select one member from Group A or B and see how that item is related to all the individual items or the opposite Group.