CONCATENATE BUT IGNORE BLANKS

Question

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. &nbsp;However, some of the 'Jurisdiction' rows are blank and therefore I would like to include in my code the condition&nbsp;to ignore the empty Jurisdiction rows and only make concatenation when values appear, e.g. instead of " &amp; Isle of Man" it say just "Isle of Man" if one is blank.&nbsp;&nbsp;Could anyone please indicate where should I include this part in my code?&nbsp;List of Juristiction values = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Lead'[Juristiction])VAR __MAX_VALUES_TO_SHOW = 2RETURNIF(ISFILTERED(Lead[Project]),&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; __DISTINCT_VALUES_COUNT &gt; __MAX_VALUES_TO_SHOW,&nbsp; &nbsp; &nbsp; &nbsp; CONCATENATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONCATENATEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; __MAX_VALUES_TO_SHOW,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Lead'[Juristiction]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Lead'[Juristiction],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ASC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Lead'[Juristiction],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ", ",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Lead'[Juristiction],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ASC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ", etc."&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; CONCATENATEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Lead'[Juristiction]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Lead'[Juristiction],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; " &amp; ",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Lead'[Juristiction],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ASC&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ), "All Jurisdictions")

alb · Accepted Answer

Hi taralouise123&nbsp;
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] &lt;&gt; "")
if it's not actual blanks but empty strings
&nbsp;

Please accept the solution when done and consider giving a thumbs up if posts are helpful.&nbsp; 
Contact me privately for support with any larger-scale BI needs, tutoring, etc.

&nbsp;

Forum Discussion

CONCATENATE BUT IGNORE BLANKS

2 Replies

Related content

Concatenate EXCEPT where blank

Ignore Blanks in Measure Result

Rankx Ignore Blanks

COMBINEVALUES ignore blank

CONCATENATE EXCEPT BLANK VALUES