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 Everyone,
I'm in need of assistance, as I have two tables that I'm working with... My Entire_Portfolio_Table has all loans in our portfolio, and my Modification_Table only contains loans that have ever been modified.
The Modification Table, does consist of multiple Modification Types, for various reasons, throughout the lifecycle of each loan. I need to pull in the most recent ModType by Date (from the Modification_Table), into my Entire_Portfolio_Table.
Modification_Table
Leadid | ModType | ModDate |
0002 | PmtHoliday | 2/1/2020 |
0002 | SubsequentMod | 3/15/2020 |
0002 | PermanentMod | 5/14/2020 |
0003 | CovidMOD | 4/1/2020 |
0005 | PmtHoliday | 1/20/2020 |
0005 | CovidMOd | 4/1/2020 |
0005 | PermanentMod | 6/1/2020 |
Entire_Portfolio_Table:
Leadid | DateFunded | ModType | ModDate |
0001 | 1/2/2017 | null | null |
0002 | 1/2/2017 | PermanentMod | 5/14/2020 |
0003 | 2/12/2017 | CovidMOD | 4/1/2020 |
0004 | 2/14/2017 | null | null |
0005 | 3/1/2017 | PermanentMod | 6/1/2020 |
The result i need would be whats shown in the Portfolio Table, above.
Note: My initial DAX formulas found the most recent ModType, but it eliminated the loans that didn't have a mod, but I need a snapshot of all loans:
Solved! Go to Solution.
Hi @qbarnes ,
Create two column as below.
Column = CALCULATE(MAX('Table'[ModDate]),FILTER('Table','Table'[Leadid]='Table (2)'[Leadid]))
Column 2 = CALCULATE(MAX('Table'[ModType]),FILTER('Table','Table'[ModDate]='Table (2)'[Column]))
Result would be shown as below.
Best Regards,
Jay
Hi @qbarnes ,
Create two column as below.
Column = CALCULATE(MAX('Table'[ModDate]),FILTER('Table','Table'[Leadid]='Table (2)'[Leadid]))
Column 2 = CALCULATE(MAX('Table'[ModType]),FILTER('Table','Table'[ModDate]='Table (2)'[Column]))
Result would be shown as below.
Best Regards,
Jay
Awesome!! Thank you so much!!
@qbarnes - Sounds like you want Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Thanks for the assist. I'm not calculating any numeric values, so how would I transform this portion of the formula, and the references to:
"__Value",SUM('Table'[Value])
I basically need to pull in all loans where MODType and ModDate are applicable, for the entire portfolio. If a loan never had a Modfication i need to maintain that as a blank value as well, to have a full snapshot of what does and does not have a ModType.
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |