Conditional merge columns

[smacsween]

i am trying to merge columns based on an IF statement and ISBLANK.  If I have Country name in Table1, use that value, if not, use the Country value from Table2.

I can't see how to do this, I have created a measure but it does not give any results

OfficeCombined =

IF (

isblank(SELECTEDVALUE ( 'Table1'[Country] ) ),

SELECTEDVALUE ( Table2[Country] ),

SELECTEDVALUE ( 'Table1'[Country] )

)

What am I doing wrong? it's driving me crazy, seems so simple but I can't figure out DAX

[smacsween]

What I am trying to do is create a new text column:

Let's see if I can describe it differently.  If the text already exists in a column from Table 1, use that. If that column in Table 1 is null or blank, instead take the text from the relevant column in Table 2. 

The Union function gives me both together, and I end up with different rows but containing identical data.  I only need one or the other because the field will be present in at least one of the columns, maybe both.

[Icey · Accepted solution]

Hi @smacsween ,

Just try this:

NewTable =
DISTINCT (
    UNION ( DISTINCT ( Table1[Country] ), DISTINCT ( Table2[Country] ) )
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Icey]

Hi @smacsween ,

SELECTEDVALUE('Table1'[Country]) will only return the countries included in Table1, and will not compare the countries in Table1 and Table2 to see if they are included. Same as SELECTEDVALUE('Table2'[Country]).

So, if "Country 1" is only contained in Table2, SELECTEDVALUE('Table1'[Country]) will not return blank for it.

It is suggested to create a table like below and create relationships among them:

NewTable =
UNION ( DISTINCT ( Table1[Country] ), DISTINCT ( Table2[Country] ) )

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[smacsween]

It's two text columns I want to merge so actually a measure is probably not the thing I should have tried, it should just be a new column.  What would be the corresponding formula for a text merge based on that scenario?

[mahoneypat]

It's a calculated column?  If so, take off the MIN( ) for whichever table you are putting it on.  For example, if adding the column to Table 2,

OfficeCombined =

IF (

isblank(MIN ( 'Table1'[Country] ) ),

Table2[Country] ,

MIN ( 'Table1'[Country] )

)

Regards,

Pat

[mahoneypat]

You said this is a measure, so I assume you are using it in a table visual.  What is the relationhip between Table1 and Table2.  You could try this instead, but will depend on the relatinship.

OfficeCombined =

IF (

isblank(MIN ( 'Table1'[Country] ) ),

MIN ( Table2[Country] ),

MIN ( 'Table1'[Country] )

)

Regards,

Pat