topic Re: DAX help - create column with minimum value so far inside a group in DAX Commands and Tips

DAX help - create column with minimum value so far inside a group

stalerik — Mon, 03 May 2021 14:25:09 GMT

Hello all,

I am looking for some help to create a calculated column which holds the minimum value recorded in a group.

I have an Index, Group, and Value column. The Lowest column should be the minimum value recorded in that group up to the index.

I thought this would be easy by filtering the Index to calculate the minumum value (MINX) between Index between Group and current Index but I just get zero. I think it's finding the minumum value in the whole range insteas of evaluating row by row.

Here is some data to explain hwo the lowest should work:

Index	Group	Value	Lowest
23	23	6	6
24	23	3	3
25	23	8	3
26	23	9	3
27	27	2	2
28	27	4	2
29	27	3	2
30	27	0	0
31	27	1	0
32	32	6	6
33	32	5	5
34	32	3	3
35	32	4	3
36	32	7	3
37	32	9	3
38	32	1	1

Thanks for any ideas and help!

Re: DAX help - create column with minimum value so far inside a group

Jihwan_Kim — Mon, 03 May 2021 14:35:38 GMT

Hi, stalerik

Please try the below.

Lowest CC =
VAR currentgroup = 'Table'[Group]
RETURN
CALCULATE (
MIN ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Group] = currentgroup
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: DAX help - create column with minimum value so far inside a group

stalerik — Mon, 03 May 2021 15:23:11 GMT

Thank you very much again Jihwan_Kim . This is the second time you have helped me quickly with a problem. Your solutions have been fantastic and understandable, and I appreciate the help!