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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
taralouise123
Helper I
Helper I

CONCATENATE BUT IGNORE BLANKS

I have the following code, in which I am creating a new column with the purpose of combining variables with different jurisdictions together to use with a slicer filter.

 

However, some of the 'Jurisdiction' rows are blank and therefore I would like to include in my code the condition to ignore the empty Jurisdiction rows and only make concatenation when values appear, e.g. instead of " & Isle of Man" it say just "Isle of Man" if one is blank.

 

 

Could anyone please indicate where should I include this part in my code?

 

List of Juristiction values =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Lead'[Juristiction])
VAR __MAX_VALUES_TO_SHOW = 2
RETURN
IF(ISFILTERED(Lead[Project]),
    IF(
        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
        CONCATENATE(
            CONCATENATEX(
                TOPN(
                    __MAX_VALUES_TO_SHOW,
                    VALUES('Lead'[Juristiction]),
                    'Lead'[Juristiction],
                    ASC
                ),
                'Lead'[Juristiction],
                ", ",
                'Lead'[Juristiction],
                ASC
            ),
            ", etc."
        ),
        CONCATENATEX(
            VALUES('Lead'[Juristiction]),
            'Lead'[Juristiction],
            " & ",
            'Lead'[Juristiction],
            ASC
        )
    ), "All Jurisdictions")
1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Hi @taralouise123 

Just filter out the blanks on the table you are passing as first argument to CONCATENATEX

FILTER(VALUES('Lead'[Juristiction]),  NOT ISBLANK('Lead'[Juristiction]))

or

FILTER(VALUES('Lead'[Juristiction]),  'Lead'[Juristiction] <> "")

if it's not actual blanks but empty strings

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

2 REPLIES 2
taralouise123
Helper I
Helper I

Thanks a lot! It worked! 

AlB
Community Champion
Community Champion

Hi @taralouise123 

Just filter out the blanks on the table you are passing as first argument to CONCATENATEX

FILTER(VALUES('Lead'[Juristiction]),  NOT ISBLANK('Lead'[Juristiction]))

or

FILTER(VALUES('Lead'[Juristiction]),  'Lead'[Juristiction] <> "")

if it's not actual blanks but empty strings

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors