cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JustinDoh1
Helper I
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:

Tables.png

 

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
daxer
Solution Sage
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
            )
        )
)

View solution in original post

4 REPLIES 4
daxer
Solution Sage
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
            )
        )
)

View solution in original post

Fowmy
Super User IV
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

@Fowmy 

I am getting this message.

Earlier.png

@JustinDoh1 

 

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

Fowmy_0-1619469716846.png

 



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

Proud to be a Super User!

Website   YouTube    LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

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