cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gcheuk Regular Visitor
Regular Visitor

Allselect except one slicer

Hi,

 

I have a simple table which I want to create a measure.

An extract of the table is here:

dax_struggle.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I would like to create a measure, so that when I filter on product, it will return the sales of all the product that has the same color as the chosen product in 2010. E.g. If I filter on Memo, the measure will return me the sales of all  stationary that is blue in 2010, in this case will be 1883.

 

I figured out ALLSELECTED might do the trick, but the challenge is I have different classes of product, so I want to keep the "Class" filter here i.e. only the blue stationary but not other class of products that are blue.

 

I tried:

 

CALCULATE(
SUM(Sales[Sales]),
FILTER(ALLSELECTED(Sales),Sales[Year] = 2010)
)

 

But this will also filter on the chosen product i.e. gives me 332 if I select memo. Essentially I would like to keep the "Class" filter active but not the "Product" filter.

 

This looks simple but I am really struggling to do it, can anyone please help?

1 ACCEPTED SOLUTION

Accepted Solutions
gcheuk Regular Visitor
Regular Visitor

Re: Allselect except one slicer

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! Smiley Happy

 

JC

 

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Allselect except one slicer

Hi @gcheuk 

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

gcheuk Regular Visitor
Regular Visitor

Re: Allselect except one slicer

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! Smiley Happy

 

JC

 

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,356)