Forum Discussion
Filter Latest Date and Value for each Row
I have a table that looks like the following:
| Name | Workgroup | Active | Modified Date Time |
| Kyle | A | 1 | 10/7/2018 |
| Kyle | A | 0 | 10/8/2018 |
| Kyle | B | 0 | 10/7/2018 |
| Kyle | B | 1 | 10/8/2018 |
| John | B | 1 | 10/7/2018 |
| John | B | 0 | 10/8/2018 |
| John | A | 0 | 10/7/2018 |
| John | A | 1 | 10/8/2018 |
I need the latest value for each [Workgroup] by [Name]
For example: Kyle's workgroup A was active 10/7/18, but has been de-activated on 10/8/18.
Kyle's current stats for [Workgroup] A is de-activated.
I need to then put the sum of the activations in a table that can display how many users are active in the respective workgroup.
I was able to get halfway there with a measure that filtered the max date for the active status. However, it does not work for the table.
7 Replies
So, can you explain the expected results from the sample data presented and to logic to arrive at that result?
Hi Greg_Deckler
I need the current status for each user and their workgroup. I was able to filter for the latest modified date time for each workgroup using this measure:
CALCULATE(SUM( 'Workgroup Table'[ActivationFlag]), FILTER('Workgroup Table', 'Workgroup Table'[ActivationDateTime]=MAX('Workgroup Table'[ActivationDateTime])))
This is what the table looks like:
I need this to only show me the most current value for of ActivationFlag by filtering ActivationDateTime for each name and each workgroup.
I think you basically have it - does it work if you use LASTNONBLANK instead of SUM in the calc?
CALCULATE(LASTNONBLANK(Table1[Active],Table1[Active]),FILTER(Table1,MAX(Table1[Modified Date Time])=Table1[Modified Date Time]))
