topic Re: Calculated Colum: Get MIN value from a filtered table in DAX Commands and Tips

Calculated Colum: Get MIN value from a filtered table

derekmalag — Fri, 03 Dec 2021 12:58:35 GMT

Hi, I'm stuck in something that I think it is simple but I can't make it work so I'm pretty sure I'm doing something incorrectly but don;t know what so would appreciatte any light on it.

I have a Table with two columns and I want to create a calculated column that get the Min value of the Model. The expected output is

Model	Total_Value	MIN_VALUE
A	3 *	3
A	4	3
B	5	2
B	2*	2
C	9	7
A	6	3
C	7*	7
B	8	2

So I've tried the following DAX query for the calculated column

MIN_VALUE = CALCULATE ( MIN('MyTable'[Total_Value]), FILTER('MyTable',[Model]=[Model]) )

However, what I get is the minimum value of the table without being filtered (2 for all values).

I would appreciate any help on what I am doing wrong.

Many thanks in advance

Re: Calculated Colum: Get MIN value from a filtered table

Greg_Deckler — Fri, 03 Dec 2021 13:37:58 GMT

derekmalag Here are a couple different ways:

MIN_VALUE = CALCULATE ( MIN('MyTable'[Total_Value]), FILTER('MyTable',[Model]=EARLIER('MyTable'[Model])) ) MIN_VALUE = VAR __Model = [Model] RETURN CALCULATE ( MIN('MyTable'[Total_Value]), FILTER('MyTable',[Model]=__Model) )

Re: Calculated Colum: Get MIN value from a filtered table

HotChilli — Fri, 03 Dec 2021 13:55:21 GMT

Greg_Deckler did I just catch you using a "CALCULATE"?

can't we rely on anything any more?....

Re: Calculated Colum: Get MIN value from a filtered table

Greg_Deckler — Fri, 03 Dec 2021 14:17:31 GMT

HotChilli Ha! Good one. I was actually going to include a version using MINX but figured it was overkill. Was easier just to correct the version of the formula that used CALCULATE since it was already there.

Re: Calculated Colum: Get MIN value from a filtered table

derekmalag — Fri, 03 Dec 2021 16:58:53 GMT

amazing Greg_Deckler , I've used the [EARLIER] option and it works so not going through the second :). Assuming not, but just to be sure, if there is any consideration in performance between these two options?

Many thanks again

Re: Calculated Colum: Get MIN value from a filtered table

Greg_Deckler — Fri, 03 Dec 2021 19:21:51 GMT

derekmalag In my testing, no I have not seen a performance difference between the two approaches. You might be interested in this: In Defense of EARLIER - Microsoft Power BI Community

Re: Calculated Colum: Get MIN value from a filtered table

AlexisOlson — Fri, 03 Dec 2021 23:06:02 GMT

derekmalag If you're concerned about performance, then you should filter on columns rather than tables whenever possible.

I'd probably write it like this instead:

MIN_VALUE = CALCULATE ( MIN ( 'MyTable'[Total_Value] ), ALLEXCEPT ( 'MyTable', 'MyTable'[Model] ) )

Re: Calculated Colum: Get MIN value from a filtered table

Anonymous — Sat, 04 Dec 2021 10:41:43 GMT

Here is the simple one line solution:

Min_Value = CALCULATE(MIN('Table'[Total Value]),FILTER('Table',EARLIER('Table'[Model])='Table'[Model]))