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.
This is what my data looks like:
Account | ID | Quarter | Mapped Account Name |
ABC Inc | ID143 | 20Q3 | |
ABC Inc. | ID143 | 20Q4 | |
ABC Incorp | ID143 | 21Q1 | |
ABC | ID143 | 21Q2 |
My goal for the Mapped Account Name column is to return the account name associated with the latest quarter based on the ID. As you can see these are all the same account, but the naming convention over the quarters has changed. In this particular case, I want to return "ABC" because that is the name used in the most recent quarter, but my Lookupvalue isn't working correctly yet.
This is what I have:
Solved! Go to Solution.
This works as a calculated column. If you want to use it as a measure, then you need an aggregating function for Account_Data_Total[ERM ID] in your definition of VAR erm.
Try this:
Mapped Account Name =
VAR erm = SELECTEDVALUE ( Account_Data_Total[ERM ID] )
VAR maxqtr =
CALCULATE (
MAXX ( Account_Data_Total, Account_Data_Total[Quarter] ),
FILTER ( Account_Data_Total, Account_Data_Total[ERM ID] = erm )
)
RETURN
LOOKUPVALUE (
Account_Data_Total[Account],
Account_Data_Total[ERM ID], erm,
Account_Data_Total[Quarter], maxqtr
)
HI @Anonymous
Try this code to add a new column to your table:
Mapped Account Name =
CALCULATE (
MAX ( [Account] ),
FILTER (
'Table',
'Table'[ID] = EARLIER ( 'Table'[ID] )
&& 'Table'[Quarter]
= CALCULATE ( MAX ( 'Table'[Quarter] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
)
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
This works as a calculated column. If you want to use it as a measure, then you need an aggregating function for Account_Data_Total[ERM ID] in your definition of VAR erm.
Try this:
Mapped Account Name =
VAR erm = SELECTEDVALUE ( Account_Data_Total[ERM ID] )
VAR maxqtr =
CALCULATE (
MAXX ( Account_Data_Total, Account_Data_Total[Quarter] ),
FILTER ( Account_Data_Total, Account_Data_Total[ERM ID] = erm )
)
RETURN
LOOKUPVALUE (
Account_Data_Total[Account],
Account_Data_Total[ERM ID], erm,
Account_Data_Total[Quarter], maxqtr
)
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |