Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Finding Common attributes between two different Datasets

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

 

 

GroupABreakfastLunchDinner GroupBBreakfastLunchDinner
A1CerealPastaVegetables B1CerealPastaVegetables
A2EggsFast foodMeat B2FruitFast foodBread
A3FruitRiceBread B3CerealFast foodMeat
A4CerealSandwichMeat B4EggsSandwichBread

 

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. 

 

Need Help
  • TomMartens's avatar
    TomMartens
    5 years ago

    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

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    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's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.