Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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)
)
Here is the simple one line solution:
@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.
@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
@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] )
)
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |