Question On Delimiters

[Maxwell]

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

[OwenAuger · Accepted solution]

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
    Concatenated

 Regards,

Owen

[Maxwell]

Thank you Owen!

Worked like a charm!