Forum Discussion
Calculated Colum: Get MIN value from a filtered table
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
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) )
7 Replies
- Greg_Deckler
Community Champion
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) )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
- Greg_Deckler
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
- HotChilli
- Greg_Deckler
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.
