cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kylebi1 Frequent Visitor
Frequent Visitor

Filter Latest Date and Value for each Row

I have a table that looks like the following:

 

NameWorkgroupActiveModified Date Time
KyleA110/7/2018
KyleA010/8/2018
KyleB010/7/2018
KyleB110/8/2018
JohnB110/7/2018
JohnB010/8/2018
JohnA010/7/2018
JohnA110/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. Untitled.png

7 REPLIES 7
Super User
Super User

Re: Filter Latest Date and Value for each Row

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


kylebi1 Frequent Visitor
Frequent Visitor

Re: Filter Latest Date and Value for each Row

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

 

 

kylebi1 Frequent Visitor
Frequent Visitor

Re: Filter Latest Date and Value for each Row

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.

Untitled1.png

MarkLaf Member
Member

Re: Filter Latest Date and Value for each Row

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

Re: Filter Latest Date and Value for each Row

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


kylebi1 Frequent Visitor
Frequent Visitor

Re: Filter Latest Date and Value for each Row

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 Member
Member

Re: Filter Latest Date and Value for each Row

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