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...
  • PaulDBrown's avatar
    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