topic DAX - ConcatenateX Function - Check if there's value first and/or value is null and/or blank... in Developer

DAX - ConcatenateX Function - Check if there's value first and/or value is null and/or blank...

arelf27 — Fri, 15 Sep 2017 16:48:29 GMT

I have the following code that works (VAR X = CONCATENATEX(Table, Table[Column] & " - " & Table[Column], "; ", [Table Column], DESC) ---however sometimes my result looks like this:

Ex. 1: Client ABC - 0; -; or

Ex. 2: -;

Basically it tries to concatenate data from one column (description) based off of the id from another column (id) - but while it matches on the id the description column is blank... So I get the ugly -; without anything following it.. So how can I check first whether there's an actual value before attempting to concatenate.. I'm new to DAX so don't know the syntax at all.

Thanks,

I simply want to check if current row Table[Column] is blank, null, etc. before doing this step: Table[Column] & " - " & Table[Column], "; ", [Table Column].... Need syntax...

Re: DAX - ConcatenateX Function - Check if there's value first and/or value is null and/or blank...

Eric_Zhang — Mon, 18 Sep 2017 02:07:07 GMT

@arelf27 wrote:

I have the following code that works (VAR X = CONCATENATEX(Table, Table[Column] & " - " & Table[Column], "; ", [Table Column], DESC) ---however sometimes my result looks like this:

Ex. 1: Client ABC - 0; -; or

Ex. 2: -;

Basically it tries to concatenate data from one column (description) based off of the id from another column (id) - but while it matches on the id the description column is blank... So I get the ugly -; without anything following it.. So how can I check first whether there's an actual value before attempting to concatenate.. I'm new to DAX so don't know the syntax at all.

Thanks,

I simply want to check if current row Table[Column] is blank, null, etc. before doing this step: Table[Column] & " - " & Table[Column], "; ", [Table Column].... Need syntax...

@arelf27

Try to apply a filter to the table.

New_Measure =
CONCATENATEX (
    FILTER ( 'Table', 'Table'[description] <> BLANK () && 'Table'[id] <> BLANK () ),
    'Table'[description] & "-"
        & 'Table'[id],
    ";",
    'Table'[description], ASC
)

Re: DAX - ConcatenateX Function - Check if there's value first and/or value is null and/or blank...

arelf27 — Mon, 25 Sep 2017 17:55:43 GMT

Thanks using Filter did the trick!

Re: DAX - ConcatenateX Function - Check if there's value first and/or value is null and/or blank...

Anonymous — Sun, 12 Mar 2023 17:00:26 GMT

Thanks, this is exactly what I needed.