Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Allselect except one slicer

Hi,   I have a simple table which I want to create a measure. An extract of the table is here:                               I would like to create a measure, so that when I ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi AlB ,

     

    Thank you for your answer. Unfortunately the measure doesn't work. It returns an error saying that multiple value was returned while a single value was expected. However, I do manage to create a measure that works based on your idea:

     

     

    Measure = 

VAR _Color = CALCULATE(SELECTEDVALUE(Sales[Color]), Sales[Year] = 2010)

RETURN
CALCULATE(
           SUM(Sales[Sales]),
           ALL(Sales[Product]),
           Sales[Color] = _Color,
           Sales[Year] = 2010
)

    Thank you again for your help! :smileyhappy:

     

    JC

     

AlB's avatar
AlB
Community Champion
7 years ago

Hi Anonymous 

Assuming all fields are on the Sales table, try this measure in a card visual, with the two slicers you show: 

Measure =
VAR _Color =
    CALCULATE ( DISTINCT ( Sales[Color] ), Sales[Year] = 2010 )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        FILTER ( ALL ( Sales[Color] ), Sales[Color] = _Color ),
        Sales[Year] = 2010,
        ALL ( Sales[Product] )
    )

If this doesn't work please show a  sample of your tables. Always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi AlB ,

 

Thank you for your answer. Unfortunately the measure doesn't work. It returns an error saying that multiple value was returned while a single value was expected. However, I do manage to create a measure that works based on your idea:

 

 

Measure = 

VAR _Color = CALCULATE(SELECTEDVALUE(Sales[Color]), Sales[Year] = 2010)

RETURN
CALCULATE(
           SUM(Sales[Sales]),
           ALL(Sales[Product]),
           Sales[Color] = _Color,
           Sales[Year] = 2010
)

Thank you again for your help! :smileyhappy:

 

JC