Dynamic Text Using List Separated by Commas and or Before Last Value

[heatherkw]

Hello,

A long timeago I found something that provided the solution I'm looking for, but I can't seem to find it again. I have a slicer for program type and users can select 1-5 values.

I am using dynamic text in a tooltip and want it to display like this if they choose two program types:

In 2020, XX individuals exited from Emergency Shelter or Transitional Housing projects. 

Then, I want it to display like this if they choose 3+ program types: 

In 2020, XX individuals exited from Emergency Shelter, Transitional Housing, or Permanent Housing projects.

For the life of me, I can't figure out how to get the "or" before only the last value in the list when they select 3+ types.

I have used variations of the following code, but nothing has worked. For the second one, I tried doing something else with concatenate when the number of selected projects was 3+, but it didn't work. I know the solution is simple, I just can't figure it out!

ProjSelectionTitle = IF(ISFILTERED(ExitsPH_HUD[RecodePgmType]), CONCATENATEX(VALUES(ExitsPH_HUD[RecodePgmType]), ExitsPH_HUD[RecodePgmType], ", ", ExitsPH_HUD[RecodePgmType], ASC), "all service types")

ProjSelectionTitle2 = 
VAR SelectedProj = VALUES(ExitsPH_HUD[RecodePgmType])
VAR NumberOfSelectedProj = COUNTROWS (SelectedProj)
VAR AllButLastProj = TOPN(NumberOfSelectedProj - 1, SelectedProj)
VAR LastSelectedProj = EXCEPT (SelectedProj, AllButLastProj)
RETURN
IF(NumberOfSelectedProj = 2, CONCATENATEX(VALUES(ExitsPH_HUD[RecodePgmType]), ExitsPH_HUD[RecodePgmType], " or ", ExitsPH_HUD[RecodePgmType], ASC),
IF(NumberOfSelectedProj = 1, LastSelectedProj))

[Sergii24]

Hi @heatherkw, here is the way to create a measure that dynamically contains multiple selected string values:

Dynamic Title Measure = CONCATENATEX( Projects, Projects[Project Title], "," )

Original solution has been proposed by Matt Allington in this community post.

[heatherkw]

Hi, thanks, this I can already do. The issue is that when there are more than 2 in the list, I want to have "and" before the last selection in the list as this is going to be in a sentence, so needs to be grammatically correct. An example would be "Emergency Shelter, Transitional Housing, and Rapid Re-Housing." I know it's possible because I found a solution sometime last year when I was working on something else but I just can't find it anymore. 

[Sergii24]

Well, we can modify the same measure:

Dynamic Title Measure = 
VAR _NumOfProjectsSelected = COUNTROWS( Projects )

VAR _AllSelectedProjectsExceptLastOne =                         //this 1 column table will have all projects except 1 selected, so we'll use CONCATENATEX later for this one
    SELECTCOLUMNS(                                              //SELECTCOLUMNS is added in case you have multiple columns in Projects table, which are not necessary in this measure
        TOPN(
            _NumOfProjectsSelected - 1,
            Projects,
            Projects[Project Title],
            ASC                                                 //important that here  you use a value different from the one in _LastSelectedProject
        ),
        "@Project Title", [Project Title]
    )

VAR _LastSelectedProject =                                      //this 1 column table will always have only 1 row
    SELECTCOLUMNS(
        TOPN(
            1,
            Projects,
            Projects[Project Title],
            DESC                                                //important that here  you use a value different from the one in _AllSelectedProjectsExceptLastOne
        ),
        "@Project Title", [Project Title]
    )

RETURN 
    IF(
        _NumOfProjectsSelected > 3,
        "More than 3 projects selected",
        IF(                                                    
            _NumOfProjectsSelected > 1,
            CONCATENATEX( _AllSelectedProjectsExceptLastOne, [@Project Title], ", " ) & " and " & _LastSelectedProject,
            _LastSelectedProject
        )
    )

I hope it is what you've been looking for! 🙂

[heatherkw · Accepted solution]

Thanks! This helped lead me down the right path. I still had to do some tweaking, but this works for me based on how it's set up in my dashboard.

ProjectSelection_Exits = 
VAR SelectedProj = VALUES(ExitsPH_HUD[RecodePgmType])
VAR NumberOfSelectedProj = COUNTROWS (SelectedProj)
VAR AllButLastProj = TOPN(NumberOfSelectedProj - 1, SelectedProj)
VAR LastSelectedProj = EXCEPT(SelectedProj, AllButLastProj)
RETURN
IF(NumberOfSelectedProj = 3 || NumberOfSelectedProj = 4, CONCATENATEX(AllButLastProj, ExitsPH_HUD[RecodePgmType], ", ") & ", or " & LastSelectedProj, 
IF(NumberOfSelectedProj = 2, CONCATENATEX(AllButLastProj, ExitsPH_HUD[RecodePgmType], ", ") & " or " & LastSelectedProj, 
IF(NumberOfSelectedProj = 1, LastSelectedProj, "all service types")))

[Sergii24]

@heatherkw, here is a modification for another text when more than 3 projects are selected:

Dynamic Title Measure = 
    IF(
        COUNTROWS( Projects ) > 3,
        "More than 3 projects selected",
        CONCATENATEX( Projects, Projects[Project Title], "," )
    )

Good luck! 🙂

[foodd]

Please create a pbix file that contains some sample data but still reflects your data model (tables, relationships, calculated columns, and measures), upload the pbix to Onedrive or Dropbox, and share the link.  Please use Excel to create the sample data instead of the manual input method share the xlsx as well.

Describe the expected results based on the sample you provide.