topic Re: How to define the value for each row in group based on another fields? in DAX Commands and Tips

How to define the value for each row in group based on another fields?

Alyona_BI — Wed, 02 Sep 2020 12:33:15 GMT

Hi guys!

I have this table with data:

Item	Name	State	Date
A	Ted	New	01.08.2020
A	Kate	New	02.08.2020
A	Kate	Active	04.08.2020
A	Ann	Active	03.08.2020
A	Ann	Closed	05.08.2020
B	Kate	New	07.08.2020
B	Kate	Active	12.08.2020
B	Adrew	Active	13.08.2020
B	Peter	Active	14.08.2020
B	Kate	Closed	15.08.2020
C	Ted	New	05.08.2020
C	Ted	Active	15.08.2020
C	Andrew	Active	06.08.2020
C	Ann	Active	08.08.2020
C	Ann	Closed	16.08.2020

For each row within Item group i need to define Name as the one with "Active" State and minimum Date (for this Item).

So the desired result should be:

Item	Name	State	Date	First Name with Active State for Item
A	Ted	New	01.08.2020	Ann
A	Kate	New	02.08.2020	Ann
A	Kate	Active	04.08.2020	Ann
A	Ann	Active	03.08.2020	Ann
A	Ann	Closed	05.08.2020	Ann
B	Kate	New	07.08.2020	Kate
B	Kate	Active	12.08.2020	Kate
B	Adrew	Active	13.08.2020	Kate
B	Peter	Active	14.08.2020	Kate
B	Kate	Closed	15.08.2020	Kate
C	Ted	New	05.08.2020	Andrew
C	Ted	Active	15.08.2020	Andrew
C	Andrew	Active	06.08.2020	Andrew
C	Ann	Active	08.08.2020	Andrew
C	Ann	Closed	16.08.2020	Andrew

Could anyone help me to do this with help of DAX and calculated column?

Thanks in advance!

Re: How to define the value for each row in group based on another fields?

Greg_Deckler — Wed, 02 Sep 2020 13:02:14 GMT

Alyona_BI - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __Previous

In your case, maybe:

First Name = VAR __Table = FILTER('Table',[Item]=EARLIER([Item]) && [State]="Active") VAR __MinDate = MINX(__Table,[Date]) RETURN MAXX(FILTER(__Table,[Date]=__MinDate),[Name])

Re: How to define the value for each row in group based on another fields?

Alyona_BI — Thu, 03 Sep 2020 06:23:42 GMT

Greg_Deckler you are really super user! 🙂

It works! Many thanks!

Re: How to define the value for each row in group based on another fields?

Greg_Deckler — Thu, 03 Sep 2020 10:48:47 GMT

Alyona_BI - Glad to help! 🙂