topic Re: Filter option (in Calculate) to take Max range only in DAX Commands and Tips

Filter option (in Calculate) to take Max range only

JustinDoh1 — Mon, 26 Apr 2021 18:49:52 GMT

(Sorry, this is a duplicate question that I posted few days ago, but I am posting again because I did not get a solution).

I am trying to create a calculated column where I originally had two ranges to bring in as condition.

I was able to create a calcuated column with both Min and Max range, but I would like to only use Max.

Here is an illustration:

I was able to use this DAX and it worked fine.

Measure =
CALCULATE(
VALUES(Star[Value]),
FILTER(
Star,
Star[Min] <= Sum(Test[Test])
&& Star[Max] >= SUM(Test[Test])
)
)

But, I would like to only use one column (Max) instead of using two columns (with Min).

Bottom is just an illustration..

MeasureNEW =
CALCULATE(
VALUES(Star[Value]),
FILTER(
Star,
Star[Max] >= MAX(Test[Test]) ??
)
)

How do I accomplish it?

Re: Filter option (in Calculate) to take Max range only

Fowmy — Mon, 26 Apr 2021 19:26:46 GMT

JustinDoh1

You cannot use VALUES(Star[Value]) as the 1st argument for CALCULATE, it has to return a single value or be an aggregation like sum, average...ets.

If you want to return the count, for example, do it this way

MeasureNEW = CALCULATE( COUNTROWS(VALUES(Star[Value])), FILTER( Star, Star[Max] >= EARLIER(Test[Test]) ) )

Re: Filter option (in Calculate) to take Max range only

JustinDoh1 — Mon, 26 Apr 2021 20:04:10 GMT

Fowmy

I am getting this message.

Re: Filter option (in Calculate) to take Max range only

Fowmy — Mon, 26 Apr 2021 20:42:06 GMT

JustinDoh1

You wanted a Calculated Column, I think you are trying as a measure.
Add as a column

Re: Filter option (in Calculate) to take Max range only

Anonymous — Mon, 26 Apr 2021 20:44:10 GMT

[Measure] = IF( HASONEVALUE( Test[Facility] ), var CurrentTestValue = SELECTEDVALUE( Test[Test] ) return CALCULATE( SELECTEDVALUE( Star[Value] ), // This TOPN should return // just one row if everything // is correctly and logically // built and this, in turn, will // make only one Value be visible // in the context, hence SELECTEDVALUE // will return only one value as it // should be. TOPN(1, FILTER( ALL( Star ), CurrentTestValue <= Star[Max] ), Star[Max], ASC ) ) )

Re: Filter option (in Calculate) to take Max range only

JustinDoh1 — Sat, 18 Sep 2021 23:49:34 GMT

Anonymous

Sorry this is late response.

But I am curious the purpose of having this column - Star[Max]?

),
Star[Max],
ASC
)

Is this just for "Sort by Star[Max] ASC"?

Thanks.

Re: Filter option (in Calculate) to take Max range only

Anonymous — Mon, 20 Sep 2021 09:47:31 GMT

TOPN

Re: Filter option (in Calculate) to take Max range only

JustinDoh1 — Mon, 20 Sep 2021 16:47:54 GMT

Anonymous

Thank you for your feedback. I guess you meant Star[Max],
ASC replaces the functionality of TOPN.