Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
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
And this is what I get if I create a calculated column on donation
@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.
Yes, How to find you on teams
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] )
@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
I checked my relationship and is correct, not sure why is not working
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |