cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 7
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?

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Frequent Visitor

## Re: Filter Latest Date and Value for each Row

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

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.

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

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

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

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 48 members 686 guests
Recent signins: