Forum Discussion

barragan82's avatar
barragan82
Icon for Helper II rankHelper II
11 months ago
Solved

Measure: Special Instructions for CountItems = 2

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!

 

 

 

Tips and Tricks
  • mh2587's avatar
    mh2587
    11 months ago
    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 )
)

3 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    11 months ago
    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's avatar
      barragan82
      Icon for Helper II rankHelper II
      11 months ago

      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's avatar
      barragan82
      Icon for Helper II rankHelper II
      11 months ago

      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?