topic Re: Data from one or another column but merged in DAX Commands and Tips

Data from one or another column but merged

adoalan — Tue, 14 Mar 2023 17:20:54 GMT

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.

Re: Data from one or another column but merged

tamerj1 — Tue, 14 Mar 2023 12:34:50 GMT

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

Re: Data from one or another column but merged

adoalan — Tue, 14 Mar 2023 17:19:39 GMT

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

Re: Data from one or another column but merged

tamerj1 — Tue, 14 Mar 2023 17:54:15 GMT

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

Re: Data from one or another column but merged

adoalan — Wed, 15 Mar 2023 09:47:13 GMT

tamerj1HI,

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

Any suggestions

Many thanks

Re: Data from one or another column but merged

tamerj1 — Wed, 15 Mar 2023 09:59:22 GMT

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

Re: Data from one or another column but merged

adoalan — Mon, 20 Mar 2023 10:51:35 GMT

I tried tamerj1 and still doesn t work

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

Re: Data from one or another column but merged

tamerj1 — Mon, 20 Mar 2023 14:06:28 GMT

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

Re: Data from one or another column but merged

adoalan — Mon, 20 Mar 2023 16:57:02 GMT

Here you go tamerj1

Re: Data from one or another column but merged

tamerj1 — Mon, 20 Mar 2023 18:02:44 GMT

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

Re: Data from one or another column but merged

adoalan — Tue, 21 Mar 2023 09:49:41 GMT

Hi tamerj1

Still doesn t like it

Re: Data from one or another column but merged

adoalan — Tue, 21 Mar 2023 09:59:06 GMT

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

Re: Data from one or another column but merged

tamerj1 — Tue, 21 Mar 2023 10:41:22 GMT

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.

Re: Data from one or another column but merged

adoalan — Tue, 21 Mar 2023 15:28:53 GMT

Hi tamerj1

how to find you on teams?

Cheers

Re: Data from one or another column but merged

tamerj1 — Tue, 21 Mar 2023 15:44:03 GMT

adoalan

Today is not possible. What is your time zone?

Re: Data from one or another column but merged

adoalan — Fri, 24 Mar 2023 10:03:25 GMT

tamerj1Dublin Ireland

Re: Data from one or another column but merged

tamerj1 — Fri, 24 Mar 2023 10:06:23 GMT

adoalan
Can we connect now?

Re: Data from one or another column but merged

adoalan — Fri, 24 Mar 2023 10:07:57 GMT

Yes, How to find you on teams

Re: Data from one or another column but merged

tamerj1 — Fri, 24 Mar 2023 10:17:38 GMT

https://teams.microsoft.com/l/meetup-join/19%3ameeting_MjVlNjIzMTAtZTRhZC00NTY5LWJkNjktZjZjZDBlMzhhOGEx%40thread.v2/0?context=%7b%22Tid%22%3a%220f42de29-ee3e-4e77-9057-5f7c6eaf130f%22%2c%22Oid%22%3a%225959e1b4-489a-4bbb-9a5e-b34749b31bb0%22%7d

Re: Data from one or another column but merged

adoalan — Fri, 24 Mar 2023 11:20:04 GMT

Hi tamerj1

That didn t worked.

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

Cheers