Forum Discussion
Question On Delimiters
I made a measures that combines all of the players in a golf league. I am filtering by handicap. My current Measure uses 'and' as a delimiter if there are 2 players and ',' if there are more. Here is my current Measure:
Measure =
CONCATENATEX(
DISTINCT(
Table[Player]
),
Table[Player],
IF(
DISTINCTCOUNT(
Table[Player]
)=2,
" and ",
", "
)
)
Is there a way to make it so if there are more than 2 players, the delimiter would be ',' except for the last delimiter. For example:
Instead of: Bob, John, Timmy, Ross
I Want: Bob, John, Timmy and Ross
Any help is appreciated!
-Maxwell
Hi Maxwell
Here is one way you could do it, and variations on this are certainly possible 🙂
To make this work, you do need a well-defined ordering of Players to determine which one appears after "and".
In the measure below, I used the "max" Player (based on text sorting) as the Player to appear after "and".
Measure = VAR Players = DISTINCT ( Table[Player] ) VAR NumPlayers = COUNTROWS ( Players ) VAR LastPlayer = MAXX ( Players, Table[Player] ) VAR PlayersExceptLast = EXCEPT ( Players, { LastPlayer } ) VAR Concatenated = CONCATENATEX ( PlayersExceptLast, Table[Player], ", ", Table[Player] ) & IF ( NumPlayers >= 2, " and " ) & LastPlayer RETURN ConcatenatedRegards,
Owen
2 Replies
Hi Maxwell
Here is one way you could do it, and variations on this are certainly possible 🙂
To make this work, you do need a well-defined ordering of Players to determine which one appears after "and".
In the measure below, I used the "max" Player (based on text sorting) as the Player to appear after "and".
Measure = VAR Players = DISTINCT ( Table[Player] ) VAR NumPlayers = COUNTROWS ( Players ) VAR LastPlayer = MAXX ( Players, Table[Player] ) VAR PlayersExceptLast = EXCEPT ( Players, { LastPlayer } ) VAR Concatenated = CONCATENATEX ( PlayersExceptLast, Table[Player], ", ", Table[Player] ) & IF ( NumPlayers >= 2, " and " ) & LastPlayer RETURN ConcatenatedRegards,
Owen
Thank you Owen!
Worked like a charm!
