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

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

kylebi1's avatar
kylebi1
Frequent Visitor
7 years ago

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.

  • MarkLaf's avatar
    MarkLaf
    Super User
    7 years ago

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