Forum Discussion

barragan82's avatar
barragan82
Helper 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 i...
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 )
)
mh2587's avatar
mh2587
Super 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
Helper 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!