Data from one or another column but merged

[adoalan]

Hi,

I have two columns with the same name (Business Region) in two entities (contact and account)

If it s a contact then Business Region from contact is populated and Business region from Accoutn is blank and the way around.

CONCATENATE = SELECTEDVALUE(account[Business Region]) & SELECTEDVALUE(contact[Business Region])

I need basically to concatenate the data. Also to mention is direct query not import of data.

Can you help please.

Thank you

More details:

So we have 3 entities: contact, account and donation.

When the amount from donation is linked to contact (so it's a person who donated) then it is populating the Business Region for contact but is blank for account.

And when the donation is coming from an account (a business/organization etc) the business region is populated for account entity and is blank for contact.

I basically need to get those two fields of Business Region populated in one field.

So as example let s say:

Jon Doe donated 6 euro and he is from London (only contact Business Region field is populated as this is contact)

and Analytics ISO company donated 7 and location is Belfast (only account Business Region field is populated as this is account)

My output should be:

Donor Business Region Amount

Jon Doe London 6

Analytics ISO Belfast 7

Hope now makes sense.

[adoalan]

Here you go @tamerj1 

[tamerj1]

@adoalan 

Ok. This is because the active relationship has the cardinality that does not help this calculation. I have no idea what columns are involved in which relationship but let me give it a wuick guess. Please try something like. 
Business Region =
RELATED ( contact[Business Region] ) &

CALCULATE (

MAX ( account[Business Region] ),

CROSSFILTER ( nfp_donation[column1], account[column1], NONE ),

USERELATIONSHIP ( contact[column2], account[column2] )

)

So CROSSFILTER - NONE to deactivate the active relationship and USERELATIONSHIP to activate the inactive (1 to 1) relationship 

 

[adoalan]

Hi @tamerj1 

Still doesn t like it

[adoalan]

And this is what I get if I create a calculated column on donation

[tamerj1]

@adoalan 
It won't work this way. Let me know if we can connect via teams or zoom to have a deeper look at it.

[adoalan]

Hi @tamerj1 

how to find you on teams?

Cheers

[tamerj1]

@adoalan 

Today is not possible. What is your time zone?

[adoalan]

@tamerj1Dublin Ireland

[tamerj1]

@adoalan 
Can we connect now?

[adoalan]

Yes, How to find you on teams

[tamerj1]

https://teams.microsoft.com/l/meetup-join/19%3ameeting_MjVlNjIzMTAtZTRhZC00NTY5LWJkNjktZjZjZDBlMzhhO...

[adoalan]

Hi @tamerj1 

That didn t worked.

Any ideas now that you know exactly what is in there?

Cheers

[tamerj1]

Hi @adoalan 
"If it s a contact then Business Region from contact....."     What is "it"?

[adoalan]

@tamerj1Hi,

Sorry let me try again:

So we have 3 entities: contact, account and donation.

When the amount from donation is linked to contact (so it's a person who donated) then it is populating the Business Region for contact but is blank for account.

And when the donation is coming from an account (a business/organization etc) the business region is populated for account entity and is blank for contact.

I basically need to get those two fields of Business Region populated in one field.

So as example let s say:

Jon Doe donated 6 euro and he is from London  (only contact Business Region field is populated as this is contact)

and Analytics ISO company donated 7 and location is Belfast (only account Business Region field is populated as this is account)

My output should be:

Donor                    Business Region          Amount

Jon Doe                 London                           6

Analytics ISO         Belfast                             7

Hope now makes sense.

Many thanks

[tamerj1]

Hi @adoalan 

as long as you are working at fact table (donation) transaction level then the simplest approach is to create a [Business Region] calculated column in the donation table 

Business Region =
RELATED ( account[Business Region] ) & RELATED ( contact[Business Region] )

[adoalan]

@tamerj1HI,

I Doesn t work. I can find the contact table but is not finding the account table.

Any suggestions

Many thanks

[tamerj1]

@adoalan 
You forgot to close the closing bracket after Business Region. Once added you need also to detele the very last closing bracket at the end of the line

[adoalan]

I tried @tamerj1 and still doesn t work

I checked my relationship and is correct, not sure why is not working

[tamerj1]

@adoalan 
Would you please paste a screenshot of your data model?