Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create group with overlapping entries

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.

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    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

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , 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

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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