Filter Latest Date and Value for each Row

[kylebi1]

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. 

[Greg_Deckler]

So, can you explain the expected results from the sample data presented and to logic to arrive at that result?

[kylebi1]

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.

[Greg_Deckler]

I'm thinking something along the lines of:

Measure = 
VAR __Name = MAX('Table'[Name]
VAR __Workgroup = MAX('Table'[Workgroup])
VAR __ActivationDateTime = MAX('Table'[ActivationDateTime])
RETURN
MAXX(FILTER('Table',[Name]=__Name && [Workgroup]=__Workgroup && [ActivationDateTime] = __ActivationDateTime),[ActivationFlag])

[kylebi1]

This measure produces a blank value with no errors: 

measure =

VAR __Name = MAX('Workgroup Table'[Name]) VAR __Workgroup = MAX('Workgroup Table'[Workgroup])
VAR __ActivationDateTime = MAX('Workgroup Table'[ActivationDateTime])
RETURN
MAXX(FILTER('Workgroup Table',[Name]=__Name && [Workgroup]=__Workgroup && [ActivationDateTime] = __ActivationDateTime),[ActivationFlag] )

[MarkLaf]

Sorry my reading comprehension was a bit poor yesterday - I missed the whole "get sum of active users" part.

This measure seemed to work with the test data I was using:

ActiveUsers = 
SUMX(
    SUMMARIZE('Workgroup Table','Workgroup Table'[Name],'Workgroup Table'[Workgroup]),
    VAR CurLastTime = CALCULATE(MAX('Workgroup Table'[ActivationDateTime])) RETURN 
    CALCULATE(
        LASTNONBLANK('Workgroup Table'[ActivationFlag],1),
        'Workgroup Table'[ActivationDateTime] = CurLastTime
    )
)

 *Edit: simplified - had some unnecessary code

[MarkLaf]

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]))

[kylebi1]

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])))