Create group with overlapping entries

[trevordunham]

I want to create a group from the following measure:

Activity (which has  the following options):

AA

AB

BC

CC

I want to create the following groups that contain:

A : (AA, AB)

B: (AB, BC)

C: (BC, CC)

Is there a way to create a group with overlaps such as this? When I try to just simply create the group it doesn't allow overlaps like Tableau does.

[PaulDBrown · Accepted solution]

Here are a couple of ways. 

1) Create a new independent table in the model for the Group Values  to use in the visual:

Group = 
DISTINCT(SELECTCOLUMNS('Activity Table', "Group", LEFT('Activity Table'[Activity])))

Next create the following measure:

Acitivties by Group =
VAR _T1 =
    DISTINCT (
        SELECTCOLUMNS (
            'Activity Table',
            "@Group", LEFT ( 'Activity Table'[Activity] )
        )
    )
VAR _T2 =
    VALUES ( 'Activity Table'[Activity] )
VAR _FT =
    CROSSJOIN ( _T1, _T2 )
VAR _Filtered =
    ADDCOLUMNS (
        FILTER (
            _FT,
            CONTAINSSTRING ( 'Activity Table'[Activity], [@Group] ) = TRUE ()
        ),
        "@Activity", [Activity]
    )
RETURN
    CONCATENATEX (
        FILTER ( _Filtered, [@Group] IN VALUES ( 'Group'[Group] ) ),
        [@Activity],
        ", "
    )

Add the Group[Group] field and the measure to the visual to get:

2) Creating a new table with both group and activity + a measure:

Table Method =
VAR _T1 =
    DISTINCT (
        SELECTCOLUMNS (
            'Activity Table',
            "TM Group", LEFT ( 'Activity Table'[Activity] )
        )
    )
VAR _T2 =
    VALUES ( 'Activity Table'[Activity] )
VAR _FT =
    CROSSJOIN ( _T1, _T2 )
VAR _Filtered =
    FILTER (
        _FT,
        CONTAINSSTRING ( 'Activity Table'[Activity], [TM Group] ) = TRUE ()
    )
RETURN
    _Filtered

 and the measure

Table method measure = CONCATENATEX(VALUES('Table Method'[Activity]), 'Table Method'[Activity], ", ")

I've attached the sample PBIX file

[amitchandak]

@trevordunham , You have to create a table with these duplicate values and join it with your table. Force a many to many join and filter from that new table to this table