Forum Discussion
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!
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
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 ) )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!
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?
