Create a calculated field that does something like IF ATTR([Status]) != PREVIOUS_VALUE(ATTR([Status])) and you should be all set. Any aggregation applied to a discrete dimension effectively creates a measure that you can use in other calcs.
3 of 3 people found this helpful
It turns out I was wrong in suggesting PREVIOUS_VALUE() - hat tip to Joe Mako for the correction!
I'd gotten LOOKUP() and PREVIOUS_VALUE() confused about what they returned. The proper calculation would be something like IF ATTR([Status]) != LOOKUP(ATTR([Status]),-1) THEN "Changed" ELSE "Same" END. The LOOKUP(ATTR([Status]),-1) actually checks the value of ATTR([Status]) in the previous row in the partition, which is what we want.
The PREVIOUS_VALUE() function is self-referential and effectively recursive. The value provided to the function (between the parentheses) just defines what's returned for the first row of the partition, for the rest of the partition PREVIOUS_VALUE() returns the value of the *entire calculation* from the previous row. Here's a table showing how these different calculations work, for a series of five statuses (these are the status values for "Cameron" in the Status Crosstab worksheet in the attached workbook):
ATTR([Status]) PREVIOUS_VALUE(ATTR([Status])) (on its own) PREVIOUS_VALUE(ATTR([Status])) (as used within the IF calculation) IF using PREVIOUS_VALUE (wrong) LOOKUP(ATTR([Status]),-1) IF using LOOKUP (right) A1 A1 A1 Same (A1 = A1) Null Same A2 A1 Same Changed (A2 != Same) A1 Changed A2 A1 Changed Changed (A2 != Changed) A2 Same A3 A1 Changed Changed (A3 != Changed) A2 Changed A4 A1 Changed Changed (A4 != Changed) A3 Changed
The attached workbook has some other examples showing how PREVIOUS_VALUE works.
Many thanks for your responses.
I was indeed struggling with the your first suggestion .. and had given up.
By I shall try your new solution. Really appreciate the example too.
Let me know if it works for you!