cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter based on rank

Hi @SteveCampbell,

 

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

Filter_based_on_rank

 

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

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

Highlighted

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

Community Support Team
Community Support Team

Re: Filter based on rank

Hi @SteveCampbell,

 

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

Filter_based_on_rank

 

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.

Re: Filter based on rank

Both solutions work, the selected answer is cleaner