Measures affected by slicer choice

[Anonymous]

Hello,

I have been attempting to figure out how to get my measure to respond to slicer choices.

What I am attempting to do is give the option to perform a measure calculation using user selections from a slicer. Meaning: I want to calculate the minimum for a certain group of values, but I only want it to use the values selected. This is some what of an optimization problem - but instead of the optimum result, the user wants to be able to look at all possible combinations.

For example, the following data has the same structure as what I am using:

 ITEM ID     SELLER     PRICE

     1                A           $50

     1                B           $40

     1                C           $45

     2                A           $100

     2                C           $120

     3                A           $10

     3                B           $11

     3                C            $9

My slicer filters I would like to use are the Sellers: A, B, and C. The user wants the ability to look at any combination of these three filters. The goal would be to find the minimum price for each item, and returning that seller. I have a measure set up that does this right now:

MinPriceSeller = VAR MinPrice = (MIN('Table'[Price]) RETURN CALCULATE(SELECTEDVALUE('Table'[Seller]), 'Table'[Price] = MinPrice)

While this returns the value, it does not provide the ability to use only the sellers I choose in the calculation. For example, running that would tell me that seller B has the min price for item 1, seller A has the min price for item 2, and seller C has the min price for item 3.

But, I want the measure to allow the user to exclude seller B from the calculation, so that it would return: seller C has the min price for item 1, seller A has the min price for item 2, and seller C has the min price for item 3. I have been trying to figure this out, but can't come up with any way to make it work based upon slicer selections.

Thanks!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please refer to this measure:

MinPriceSeller =
VAR minprice =
    CALCULATE (
        MIN ( 'Table'[PRICE] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ITEM ID] = SELECTEDVALUE ( 'Table'[ITEM ID] )
        )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[SELLER] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[PRICE] = minprice )
    )

Best regards,

Yuliana Gu

[Anonymous]

Thank you, that works!