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.
Hello! I am going in circles. I have a table of donors and a table of gifts in a one to many relationship. In the Donor table, I would like to create a calculated column that looks at all the gifts for each donor and then writes the most recent Solicit Code (based on Gift Date) to the calculated column. My main problem is that I don't have anything to aggregrate so most of the examples don't apply.
Donor ID | Gift Date | Solicit Code |
1 | 1/1/2016 | Banquet |
1 | 1/1/2017 | Fun Run |
1 | 1/1/2018 | Appeal |
1 | 1/1/2019 | Online |
In this case, the most recent gift is 1/1/2019 so the Most Recent Solicit Code would be "Online" and that's what I would like written into the calculated column field for Donor 1.
Once I figure out how to do this, it is going to be very helpful in our data analysis, so thank you in advance for your assistance!
Solved! Go to Solution.
You can use similar basic logic, if you can guarentee that you don't have multple gifts from the one Donor on a given day you could use the relatedtable and lookup functions
Latest Solicit Code = VAR _lastDate = MAXX(RELATEDTABLE(Gift), Gift[Gift Date] ) VAR _result = LOOKUPVALUE(Gift[Solicit Code], Gift[Donor ID], Donor[Donor ID], Gift[Gift Date], _lastDate) RETURN _result
If you can't guarentee that there won't be more than one gift from an given donor on a given day you could use the following, but note that it will just grab the "max" Solicit Code (so probably the last one when sorted alphabetically)
Latest Solicit Code 2 = VAR _lastDate = MAXX(RELATEDTABLE(Gift), Gift[Gift Date] ) VAR _currentDonor = Donor[Donor ID] VAR _result = CALCULATE(MAXX(FILTER(Gift, Gift[Donor ID] = _currentDonor && Gift[Gift Date] = _lastDate) , Gift[Solicit Code])) RETURN _result
If you want to do this as a calculated column on the example table you provided above you could use the following code:
Ideally, I need the calculated column on the Donor table, not the Gift table. How do I move a string of text from a column in one table to a column in another?
Hi @Anonymous , if those tables are correctly related, you can create a matrix table on the canvas and use the columns you want.
or if you prefer, send me the pbix to give it a try.
You can use similar basic logic, if you can guarentee that you don't have multple gifts from the one Donor on a given day you could use the relatedtable and lookup functions
Latest Solicit Code = VAR _lastDate = MAXX(RELATEDTABLE(Gift), Gift[Gift Date] ) VAR _result = LOOKUPVALUE(Gift[Solicit Code], Gift[Donor ID], Donor[Donor ID], Gift[Gift Date], _lastDate) RETURN _result
If you can't guarentee that there won't be more than one gift from an given donor on a given day you could use the following, but note that it will just grab the "max" Solicit Code (so probably the last one when sorted alphabetically)
Latest Solicit Code 2 = VAR _lastDate = MAXX(RELATEDTABLE(Gift), Gift[Gift Date] ) VAR _currentDonor = Donor[Donor ID] VAR _result = CALCULATE(MAXX(FILTER(Gift, Gift[Donor ID] = _currentDonor && Gift[Gift Date] = _lastDate) , Gift[Solicit Code])) RETURN _result
Thank you! I used the second formula because we have split gifts that have the same gift date. Generally, they have the same solicit code but if they don't it's fine to use whatever the formula decides is MAX.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |