Filter option (in Calculate) to take Max range only

[JustinDoh1]

(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?

[Anonymous · Accepted solution]

[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
            )
        )
)

[JustinDoh1]

@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. 

[Anonymous]

TOPN

[JustinDoh1]

@Anonymous 

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

[Fowmy]

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

[JustinDoh1]

@Fowmy 

I am getting this message.

[Fowmy]

@JustinDoh1 

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