Forum Discussion

heatherkw's avatar
heatherkw
Icon for Helper I rankHelper I
3 years ago
Solved

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

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 usin...
Need Help
  • heatherkw's avatar
    heatherkw
    3 years ago

    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")))

     

     

heatherkw's avatar
heatherkw
Icon for Helper I rankHelper I
3 years ago

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's avatar
Sergii24
Icon for Super User rankSuper User
3 years ago

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's avatar
    heatherkw
    Icon for Helper I rankHelper I
    3 years ago

    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")))