Fix addresses by concatenating fields

[Anonymous]

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]

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

[Anonymous]

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.

[djurecicK2]

Hi @Anonymous ,

 I would suggest doing this in a calculated column, not a measure.

Can you explain what you mean by "all possibilities"?

[Anonymous]

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.

Here is the current measure:

Indirizzo Completo = 

var indirizzo=
CONCATENATE(
    CONCATENATE(
        CONCATENATE(
            CONCATENATE(
                IF(
                    NOT ISBLANK(MAX(Clienti[Indirizzo])) && LEN(MAX(Clienti[Indirizzo]))>1,
                    MAX(Clienti[Indirizzo]),
                    BLANK()
                ),
                IF(
                    NOT ISBLANK(MAX(Clienti[Comune])) && LEN(MAX(Clienti[Comune]))>1,
                    IF(
                        NOT ISBLANK(MAX(Clienti[Indirizzo])) && LEN(MAX(Clienti[Indirizzo]))>1,
                        ", " & MAX(Clienti[Comune]),
                        MAX(Clienti[Comune])
                    ),
                    BLANK()
                )
            ),
            IF(
                NOT ISBLANK(MAX('Clienti'[CAP])) && LEN(MAX('Clienti'[CAP]))>1,
                IF(
                    (NOT ISBLANK(MAX(Clienti[Indirizzo])) && LEN(MAX(Clienti[Indirizzo]))>1) ||
                    (NOT ISBLANK(MAX(Clienti[Comune])) && LEN(MAX(Clienti[Comune]))>1),
                    ", " & MAX(Clienti[CAP]),
                    MAX(Clienti[CAP])
                ),
                BLANK()
            )
        ),
        IF(
            NOT ISBLANK(MAX(Clienti[Provincia])) && LEN(MAX(Clienti[Provincia]))>1,
            IF(
                (NOT ISBLANK(MAX(Clienti[Indirizzo])) && LEN(MAX(Clienti[Indirizzo]))>1) ||
                (NOT ISBLANK(MAX(Clienti[Comune])) && LEN(MAX(Clienti[Comune]))>1) ||
                (NOT ISBLANK(MAX('Clienti'[CAP])) && LEN(MAX('Clienti'[CAP]))>1),
                ", " & MAX(Clienti[Provincia]),
                MAX(Clienti[Provincia])
            ),
            BLANK()
        )
    ),
    IF(
        NOT ISBLANK(MAX(Clienti[Paese])) && LEN(MAX(Clienti[Paese]))>1,
        IF(
            (NOT ISBLANK(MAX(Clienti[Indirizzo])) && LEN(MAX(Clienti[Indirizzo]))>1) ||
            (NOT ISBLANK(MAX(Clienti[Comune])) && LEN(MAX(Clienti[Comune]))>1) ||
            (NOT ISBLANK(MAX('Clienti'[CAP])) && LEN(MAX('Clienti'[CAP]))>1) ||
            (NOT ISBLANK(MAX(Clienti[Provincia])) && LEN(MAX(Clienti[Provincia]))>1),
            ", " & MAX(Clienti[Paese]),
            MAX(Clienti[Paese])
        ),
        BLANK()
    )
)

return
IF(NOT ISBLANK(indirizzo), indirizzo, "Nessun indirizzo disponibile")