cancel
Showing results for
Search instead for
Did you mean:  Helper I

## Filter option (in Calculate) to take Max range only

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

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?

1 ACCEPTED SOLUTION  Solution Sage
``````[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
)
)
)``````
4 REPLIES 4  Solution Sage
``````[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
)
)
)``````  Super User IV

@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])
)
)``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn  Helper I

I am getting this message.   Super User IV

You wanted a Calculated Column, I think you are trying as a measure.
Add  as a column Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group! #### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks #### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp! Top Solution Authors
Top Kudoed Authors
Users online (3,402)