Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adoalan
Helper III
Helper III

Data from one or another column but merged

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.

19 REPLIES 19
adoalan
Helper III
Helper III

Here you go @tamerj1 

 

 

adoalan_0-1679331418124.png

 

@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 

BB7EF4C8-B6FD-4029-B79F-D84D4B258CD5.jpeg

adoalan_0-1679392150339.png

adoalan_1-1679392163466.png

 

Hi @tamerj1 

Still doesn t like it

 

 

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

adoalan_2-1679392741695.png

 

@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.

Hi @tamerj1 

 

how to find you on teams?

 

Cheers

@adoalan 

Today is not possible. What is your time zone?

@tamerj1Dublin Ireland

@adoalan 
Can we connect now?

Yes, How to find you on teams

Hi @tamerj1 

 

That didn t worked.

 

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

 

Cheers

tamerj1
Super User
Super User

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

@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

 

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_0-1678873484020.png

 

@tamerj1HI,

 

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

 

Any suggestions

 

Many thanks

 

 

@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

I tried @tamerj1 and still doesn t work

adoalan_0-1679309420777.png

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors