Measure: Special Instructions for CountItems = 2

[barragan82]

Hi Everyone,

I have a measure that auto populates items. It provides a comma between each item, and includes the word "and" before the last item. I would like it so that when there are only two items it does not include a comma but does include the word "and." 

Example: 

Currently the measure displays like this: Item 1, and Item 2

I would like it to display like this: Item 1 and Item 2

I would like it to continue adding a comma and the word "and" when there are three or more items (ex. Item 1, Item 2, and Item 3). Below is a screenshot of the measure I am using. Thank you all for your help!

[mh2587 · Accepted solution]

Measure = //Try this might help you
VAR ItemsList =
    FILTER (
        DISTINCT ( 'Master Core AY24-25'[WC Assignment Type] ),
        LEN ( TRIM ( COALESCE ( 'Master Core AY24-25'[WC Assignment Type], "" ) ) ) > 0
    )
VAR CountItems = COUNTROWS ( ItemsList )
VAR ConcatText =
    CONCATENATEX (
        ItemsList,
        TRIM ( COALESCE ( 'Master Core AY24-25'[WC Assignment Type], "" ) ),
        ", ",
        TRIM ( COALESCE ( 'Master Core AY24-25'[WC Assignment Type], "" ) ),
        ASC
    )
VAR LastItem =
    MAXX ( ItemsList, TRIM ( COALESCE ( 'Master Core AY24-25'[WC Assignment Type], "" ) ) )
RETURN
SWITCH (
    TRUE (),
    CountItems = 0, BLANK(),
    CountItems = 1, LastItem,
    CountItems = 2,
        -- For exactly two items, replace comma with " and "
        SUBSTITUTE ( ConcatText, ", ", " and " ),
    SUBSTITUTE ( ConcatText, ", " & LastItem, " and " & LastItem )
)

[barragan82]

No need to reply to my previous question. I was able to get the result I was looking for by making the changes indicated in red. Just posting as FYI for the forum. Thanks!

[barragan82]

Thanks for your help, @mh2587! Unfortantely, some of my items have commas in them and are being converted to "and." For example, my Item 1 is "oranges, lemons, limes, etc.", but it is now being displayed as "oranges and lemons and limes and etc." Do you have any other suggestions?