cancel
Showing results for
Did you mean:
Highlighted
Super User I

## Filter based on rank

For some reason can't work this out

I have a table with products 1,2,3,4,5,6

I can filter on any combination

I have another table with selected product, based on rank of the product ID

I'm trying to write a measure to say which product I have selected -

For example:

products filter = 2,4,5

selected product = 2

The result should be product 4, as this is the second in the list of products filter

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Filter based on rank

Can you share a sample and the expected result? Especially the data structure. Please check out the demo in the attachment.

Measure =
VAR whichOne =
SELECTEDVALUE ( selectedProducts[Selected] )
RETURN
MAXX (
FILTER (
SUMMARIZE (
'Products',
Products[ProductName],
"ranks", RANKX (
ALLSELECTED ( 'Products' ),
CALCULATE ( SUM ( Products[ProductID] ) ),
,
ASC
)
),
[ranks] = whichOne
),
[ProductName]
)

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User I

## Re: Filter based on rank

Measure =
var _selected_product = FIRSTNONBLANK('Product Spot'[ProductSpot],99)
var _invent_prod_rank = CALCULATE(RANKX(ALLSELECTED(prods),FIRSTNONBLANK(prods[prod item],1),,ASC))
var _selected_item_table =
FILTER(ADDCOLUMNS(VALUES(prods[prod item]) "AA", _invent_prod_rank )
,[AA]=_selected_product)

was where I was going, which works but only with prod item row context

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!

Super User I

## Re: Filter based on rank

Measure =
var _selected_invent_spot = min('Inventory Spot'[Inventory Spot])

return
CALCULATE(MAX(inventory[inventory item]),
FILTER(
ADDCOLUMNS(VALUES(inventory[inventory item]),"AAA", RANKX(ALLSELECTED(inventory),inventory[inventory item],,asc) )
,[AAA]=_selected_invent_spot))

So my issue was FIRSTNONBLANK

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!

Microsoft

## Re: Filter based on rank

Can you share a sample and the expected result? Especially the data structure. Please check out the demo in the attachment.

Measure =
VAR whichOne =
SELECTEDVALUE ( selectedProducts[Selected] )
RETURN
MAXX (
FILTER (
SUMMARIZE (
'Products',
Products[ProductName],
"ranks", RANKX (
ALLSELECTED ( 'Products' ),
CALCULATE ( SUM ( Products[ProductID] ) ),
,
ASC
)
),
[ranks] = whichOne
),
[ProductName]
)

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User I

## Re: Filter based on rank

Both solutions work, the selected answer is cleaner

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors