Fix addresses by concatenating fields

Question

In my dataset are 5 fields for addresses:

Address, City, ZIPCode, Province and Country.

I would like to merge them in a single measure to be used in order to plot their location on a map visual.

What I would like however, is having to manually take into account all the possibilities using IF() or SWITCH().

Is there a function that could help me getting through this?

johnt75 · Answer

As djurecicK2 said, I would do it in a calculated column not a measure. You can get rid of blank values by using
Full address =
CONCATENATEX (
    FILTER (
        { [Address], [City], [ZIPCode], [Province], [Country] },
        NOT ISBLANK ( [Value] )
    ),
    [Value],
    ", "
)

djurecic · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;I would suggest doing this in a calculated column, not a measure.&nbsp;Can you explain what you mean by "all possibilities"?&nbsp;

anonymous · Answer

By "all possibilities" I mean some of the fields can be empty, and I would like the result to be formatted in such a way that the first field present isn't preceded by a comma, and so is the last. I have managed to write a DAX expression that returns the desired result, but it is extremely resource intensive and sadly maps do not accept measures as Location field.&nbsp;Here is the current measure:Indirizzo Completo = var indirizzo=CONCATENATE(&nbsp; &nbsp; CONCATENATE(&nbsp; &nbsp; &nbsp; &nbsp; CONCATENATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONCATENATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT ISBLANK(MAX(Clienti[Indirizzo])) &amp;&amp; LEN(MAX(Clienti[Indirizzo]))&gt;1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(Clienti[Indirizzo]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT ISBLANK(MAX(Clienti[Comune])) &amp;&amp; LEN(MAX(Clienti[Comune]))&gt;1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT ISBLANK(MAX(Clienti[Indirizzo])) &amp;&amp; LEN(MAX(Clienti[Indirizzo]))&gt;1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ", " &amp; MAX(Clienti[Comune]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(Clienti[Comune])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT ISBLANK(MAX('Clienti'[CAP])) &amp;&amp; LEN(MAX('Clienti'[CAP]))&gt;1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (NOT ISBLANK(MAX(Clienti[Indirizzo])) &amp;&amp; LEN(MAX(Clienti[Indirizzo]))&gt;1) ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (NOT ISBLANK(MAX(Clienti[Comune])) &amp;&amp; LEN(MAX(Clienti[Comune]))&gt;1),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ", " &amp; MAX(Clienti[CAP]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(Clienti[CAP])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT ISBLANK(MAX(Clienti[Provincia])) &amp;&amp; LEN(MAX(Clienti[Provincia]))&gt;1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (NOT ISBLANK(MAX(Clienti[Indirizzo])) &amp;&amp; LEN(MAX(Clienti[Indirizzo]))&gt;1) ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (NOT ISBLANK(MAX(Clienti[Comune])) &amp;&amp; LEN(MAX(Clienti[Comune]))&gt;1) ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (NOT ISBLANK(MAX('Clienti'[CAP])) &amp;&amp; LEN(MAX('Clienti'[CAP]))&gt;1),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ", " &amp; MAX(Clienti[Provincia]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(Clienti[Provincia])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; NOT ISBLANK(MAX(Clienti[Paese])) &amp;&amp; LEN(MAX(Clienti[Paese]))&gt;1,&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (NOT ISBLANK(MAX(Clienti[Indirizzo])) &amp;&amp; LEN(MAX(Clienti[Indirizzo]))&gt;1) ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (NOT ISBLANK(MAX(Clienti[Comune])) &amp;&amp; LEN(MAX(Clienti[Comune]))&gt;1) ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (NOT ISBLANK(MAX('Clienti'[CAP])) &amp;&amp; LEN(MAX('Clienti'[CAP]))&gt;1) ||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (NOT ISBLANK(MAX(Clienti[Provincia])) &amp;&amp; LEN(MAX(Clienti[Provincia]))&gt;1),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ", " &amp; MAX(Clienti[Paese]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(Clienti[Paese])&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; ))returnIF(NOT ISBLANK(indirizzo), indirizzo, "Nessun indirizzo disponibile")&nbsp;

anonymous · Answer

I have tried something myself, and got somewhere near the result, but I will try your formula as it seems much more efficient. I would've liked to use a measure as that would have allowed me to still use DirectQuery, but it looks like I'll have to switch to import mode, as map visuals do not accept measures in the Location field.

Forum Discussion

Fix addresses by concatenating fields

4 Replies

Related content

Concatenate lists ;

Concatenated Lookup

Concatenating different address together with space

concatenate strings

Group and Concatenate