Forum Discussion

kylebi1's avatar
kylebi1
Frequent Visitor
7 years ago

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 ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

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's avatar
kylebi1
Frequent Visitor
7 years ago

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's avatar
    MarkLaf
    Super User
    7 years ago

    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