Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
heatherkw
Helper I
Helper I

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

 

 

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

 

 

View solution in original post

6 REPLIES 6
Sergii24
Super User
Super User

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

 

Sergii24_0-1693165322101.png

 

 

 

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

 

 

 

 

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

 

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. 

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

 

Sergii24_0-1693240539476.png


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

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, here is a modification for another text when more than 3 projects are selected:

Sergii24_1-1693165591331.png

 

 

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

 

 

Good luck! 🙂

foodd
Super User
Super User

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.