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
taralouise123
Frequent Visitor

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
Super User
Super User

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
Frequent Visitor

Thanks a lot! It worked! 

AlB
Super User
Super User

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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