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.
I'm using lastnonblank to find a value in a table (all case statuses), but want to bring back the value in a different column in that same row. The formula is
Year End Status = CALCULATE(
MAX('All Case Statuses'[StatusID]),
FILTER(FILTER('All Case Statuses','All Case Statuses'[CaseStatusYear] <= 'Base Case Expanded'[YearEndStatusYear]),
LASTNONBLANK('All Case Statuses'[StatusID],MAX('All Case Statuses'[StatusID]))))
What I want to return is the value in the "all case statuses" table for another column instead of the "All Case Statuses[StatusID]. This formula brings back the correct value for "StatusID", but I want the value for "Description" which is in the same (All Case Statuses) table in that same row.
These two table (All Case Statuses) and (Base Case Expanded) are both related (many/one) to a (Base Case Data) table that has one entry per case using a caseID.
Solved! Go to Solution.
Perhaps LOOKUPVALUE?
https://msdn.microsoft.com/en-us/library/gg492170.aspx
Perhaps LOOKUPVALUE?
https://msdn.microsoft.com/en-us/library/gg492170.aspx
That worked, but I need to return two values (from separate columns). If I try to do two columns using the lookup function, I get an error indicating circular dependency.
The formula for the current calculated column that does the lookup is:
Year End Status = LOOKUPVALUE('All Case Statuses'[CaseStatusDescription]
,'All Case Statuses'[StatusID],(CALCULATE(
MAX('All Case Statuses'[StatusID]),
FILTER(FILTER('All Case Statuses','All Case Statuses'[CaseStatusYear] <= 'Base Case Expanded'[YearEnd]),
LASTNONBLANK('All Case Statuses'[StatusID],MAX('All Case Statuses'[StatusID])))))
This brings back the Case Status Description from the All Case Statuses table, but I also want to bring back another value (and upt in a differect column) based on the same search value.
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 |
---|---|
99 | |
98 | |
79 | |
74 | |
66 |
User | Count |
---|---|
136 | |
109 | |
104 | |
82 | |
73 |