Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
38 | |
25 | |
19 | |
15 | |
8 |
User | Count |
---|---|
69 | |
48 | |
45 | |
18 | |
16 |