Forum Discussion
Anonymous
4 years agoNot applicable
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...
- 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 _Filteredand the measure
Table method measure = CONCATENATEX(VALUES('Table Method'[Activity]), 'Table Method'[Activity], ", ")I've attached the sample PBIX file
PaulDBrown
4 years agoCommunity Champion
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