cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors