Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |