CONCATENATE BUT IGNORE BLANKS

[taralouise123]

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

[taralouise123]

Thanks a lot! It worked! 

[AlB · Accepted solution]

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

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.