cancel
Showing results for
Did you mean:
Highlighted Helper I

## Filtering based on RANKX

Hi All,

I have three calculated columns created in one table name "INVENTORY":

(1) Having ITEM values which are coming from connected second "ITEM" Table using Related function

(2) Having Grade values which are coming from connected third "GRADE" Table using Related function

(3) Calculation done on "INVENTORY" table to get Summation of few columns of inventory table row wise, name: "SUMMED VALUE"

Output is like this using Matrix visual: I need to only show one value of GRADE every row, i.e. minimum value For e.g. For ITEM =80026506 only 16A should come. While others having one Grade value have no impact.

I tried implementing this using RANKX function:

Test = CALCULATE(SUM(INVENTORY[SUMMED VALUE])) When I am applying filter on Rank = 1, it is showing no records. How can I implement this?

The reason for this I suspect is that RANKX is not working correctly on SUM(SUMMED VALUE) field.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Helper I

## Re: Filtering based on RANKX

Hi Maggie,

I could resolve this issue using below formula:

```Test =
CALCULATE (
[SUMMED VALUE],
FILTER (
INVENTORY,
NOT ( ISBLANK ( INVENTORY[GRADE] ) )
&& [SUMMED VALUE] > 0
)
)```

Then using RANKX:

```Rank =
IF (
NOT ( ISBLANK ( [Test] ) ),
RANKX (
FILTER ( ALL ( INVENTORY[GRADE] ), [Test] ),
[Test],
,
ASC,
DENSE
),
BLANK ()
)```

The issue was happening due to duplicates and blank values. It resolved the issue

3 REPLIES 3
Highlighted Community Support

## Re: Filtering based on RANKX

I can get the right result as you expected.

`sumvalue = CALCULATE(SUM(Sheet1[sum value])) rank = RANKX(ALLSELECTED(Sheet1[grade]),[sumvalue],,DESC)` Best Regards

Maggie

Highlighted Helper I

## Re: Filtering based on RANKX

Hi Maggie,

Using "DESC" in RANKX as u suggested below alows me to filter the visual. That is great but it doesn't solve my objective. Let me explain below:

I used these formulas as given by you:

```Test = CALCULATE(SUM(INVENTORY[SUMMED VALUE]))

Results are: As I mentioned: I need to show values of "SUMMED VALUE" column in visual as per this logic:

(1) This value should be >0 (As "SUMMED VALUE" can be negative and 0, show I appled visual filter on this column as greater than 0.)

(2) If for >0 there are two or more than two values it should show minimum from them (E.g. For GRADE = 80026506, two values are coming: 22 and 3242, It should show 22 only. After using DESC it is showing 3242)

(3) If for >0 only one value is there then that value should come as it is. It is coming from the DAX you shared.

Please let me know if you need PBIX file of it.

Thanks for the help!

Highlighted Helper I

## Re: Filtering based on RANKX

Hi Maggie,

I could resolve this issue using below formula:

```Test =
CALCULATE (
[SUMMED VALUE],
FILTER (
INVENTORY,
NOT ( ISBLANK ( INVENTORY[GRADE] ) )
&& [SUMMED VALUE] > 0
)
)```

Then using RANKX:

```Rank =
IF (
NOT ( ISBLANK ( [Test] ) ),
RANKX (
FILTER ( ALL ( INVENTORY[GRADE] ), [Test] ),
[Test],
,
ASC,
DENSE
),
BLANK ()
)```

The issue was happening due to duplicates and blank values. It resolved the issue

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries! #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications Top Solution Authors
Top Kudoed Authors
Users online (2,226)