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
amitchandak
4 years agoSuper User
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